RT 3.6.5 transactions created in wrong timezone

RT Users
1

I run a script (rt3-batch-stats) monthly which records TimeWorked per
ticket per user so i know much time each user spent on jobs.

Since we upgraded to 3.6.5 the times records on each transaction are 10
hours behind. This is annoying because it muddies up the figures as to
when transactions were done.
my server has timezome set correctly and syncs to an ntp server.

Even though i set /opt/rt3/etc/RT_SiteConfig.pm up with a correct
timezone…

Set($Timezone , ‘Australia/Sydney’);

it still shows wrong. I have read some posts on the list, some say its
InnoDB thats doing the GMT time and not looking at the variable in
RT_SiteConfig.pm. However nobody has a real solution.

This is from the list…

“As of 3.6.5 you have to edit lib/RT.pm to modify the function
"InitLogging” which uses gmtime(time)."

…can anyone shed some light on exactly what needs to be changed? i’m no perl expert.

TIA

Regards,

Les

2

Since we upgraded to 3.6.5 the times records on each transaction are 10
hours behind. This is annoying because it muddies up the figures as to
when transactions were done.
my server has timezome set correctly and syncs to an ntp server.

In the UI or in the database? RT stores all timestamps in GMT in the
database.

3

Jesse Vincent wrote:

In the UI or in the database? RT stores all timestamps in GMT in the
database.

In the UI it looks fine. Its in the DB that it is out of whack.

Checking database tables via phpmyadmin shows Transactions.Created to be
in wrong timezone whereas the UI shows them fine.

The script i am using is below for reference…

#!/usr/bin/perl

rt3-timecheck

select(STDOUT); $|=1;

use DBI;

my $dayinterval;

if (!defined($ARGV[0])) {
print “Usage: rt3-timecheck [username] [|] []\n”;
print " is the number of days ago to report from.\n";
print " and are in the format yyyy-mm-dd\n";
print " is an individual user to run a report for
aswell as a grand total.\n";
exit 1;
}

if (defined($ARGV[1])) {
if ($ARGV[1] =~ /-/) {
$dayfrom = “"$ARGV[1]"”;
$dayrange = "from $dayfrom to ";
if (defined($ARGV[2])) {
$dayto = “"$ARGV[2]"”;
$dayrange .= $dayto;
} else {
$dayto = “CURDATE()”;
$dayrange .= “today”;
}
} else {
$dayinterval = $ARGV[1];
$dayrange = “in the last $dayinterval days”;
$dayfrom = “DATE_SUB(CURDATE(), INTERVAL $dayinterval DAY)”;
$dayto = “CURDATE()”;
}
} else {
$dayinterval = 7;
$dayrange = “in the last $dayinterval days”;
$dayfrom = “DATE_SUB(CURDATE(), INTERVAL 7 DAY)”;
$dayto = “CURDATE()”;
}

my $database = “rt3”;
my $hostname = “localhost”;
my $port = ‘’;
my $user = “rt_user”;
my $password = “rt_pass”;

my $dsn = “DBI:mysql:database=$database;host=$hostname;port=$port”;
my $dbh = DBI->connect($dsn,
$user,
$password,
{‘RaiseError’ => 1});

my $timetotal;

$myuser = $ARGV[0];

format STDOUT_TOP =

Time Recorded on Ticket Transactions @<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
for @<<<<<<<
$dayrange,$myuser

Id Time Taken Created Creator Queue Subject
===== ========== =================== ======= ==========
.

format STDOUT =
@<<<< @<<<<<<<<< @<<<<<<<<<<<<<<<<<< @<<<<<< @<<<<<<<<<
^<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
$ary[0],$ary[1],$ary[2], $ary[3],$ary[4] $ary[5]
^<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
$ary[5]
.

$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;
}

$ary[0] = “Total”;
$ary[1] = $timetotal;
print " ===== ==========\n";
write;

$dbh->disconnect;

exit 0;

TIA

Regards,

Les

4

Jesse Vincent wrote:

In the UI or in the database? RT stores all timestamps in GMT in the
database.

In the UI it looks fine. Its in the DB that it is out of whack.

Checking database tables via phpmyadmin shows Transactions.Created to be
in wrong timezone whereas the UI shows them fine.

The script i am using is below for reference…

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.

5

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.

Fair enough, but i know nothing about perl.

This script came from the request tracker wiki -
http://wiki.bestpractical.com/view/RT3BatchStats

So can anyone help with reworking the script to convert times from GMT
to local timezone?

TIA

Regards,

Les

6

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