[rt-users] RT 3.6.5 transactions created in wrong timezone....SOLVED

[Les Stott]

>> Like Jesse said, times are stored as GMT in the DB, so if you access the
>> DB directly without using the RT API, you have to convert times to your
>> timezone manually in your script.
>>
>>     
Just letting everyone know i eventually found a way to do this after 
some hours trying to figure out a way to use perl to do it, i found you 
could do it with plain old "date".

The relevant section of the script.....where$ary[2] is 
Transaction.Created.....

###########################################################

$query = "select Transactions.ObjectId, Transactions.TimeTaken, 
Transactions.Created, Users.Name, Queues.Name, Tickets.Subject from 
Transactions, Tickets, Queues, Users where Transactions.TimeTaken!='0' 
and Transactions.ObjectId=Tickets.Id and Transactions.Creator=Users.Id 
and Users.Name='$myuser' and Transactions.Created>=$dayfrom and 
Transactions.Created<=$dayto group by Transactions.Created;";
$sth = $dbh->prepare($query) or die "Can't prepare queue query";
$rc = $sth->execute
  or die "Can't execute statement: $DBI::errstr";

while (@ary = $sth->fetchrow_array) {
        $timetotal += $ary[1];
        write;
}

############################################################

Needed to have the while loop changed to this.....

while (@ary = $sth->fetchrow_array) {
        $timetotal += $ary[1];
        $ary[2] = `date -d 'GMT $ary[2]'`;
        write;
}

That may not be the most elegant way, but it works.

Regards,

Les