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

[Les Stott]

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] [<days>|<date from>] [<date 
to>]\n";
 print "       <days> is the number of days ago to report from.\n";
 print "       <date from> and <date to> are in the format yyyy-mm-dd\n";
 print "       <username> 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