RT (Request Tracker) Date Handling
Rick Measham
rickm at printaform.com.au
Thu Nov 6 22:13:01 CST 2003
At 14:30 +1000 2003-11-07, Scott Penrose wrote:
>Hey Dudes.
>
>I am doing some manual quereis on an RT database and having problems
>with the dates and times.
>
>In an RT Ticket we have a Last Updated time of "Fri Nov 7 12:14:06 2003".
>
>If I do a query on the database I get the following:
>
>mysql> select LastUpdated, UNIX_TIMESTAMP(LastUpdated) as uts from
>Tickets where id = 12511;
>+---------------------+------------+
>| LastUpdated | uts |
>+---------------------+------------+
>| 2003-11-07 01:14:06 | 1068128046 |
>+---------------------+------------+
>1 row in set (0.00 sec)
>
>If I convert the unix time stamp there to a local time I get:
>
>scottp at lust:505:~$ localtime 1068128046
>Fri Nov 7 01:14:06 2003
>
>Note that they are the same, all 1:14 (AM).
>Note that RT shows this is actually 12:14 pm (11 hours later).
>
>I can only conclude one of two possible issues.
>
> * RT is removing 11 hours before storing in the database - thus
>invalid entries are being stored in the database.
>
>OR
>
> * MySQL is not correctly returning Unix Time Stamps
>
>Does anyone know which is likely ? And how to fix it ?
By definition, unix time stamps are seconds since 1970-01-01T00:00:00 UTC.
1068128046 = 2003-11-06T14:14:06
But it should be 2003-11-07T12:14.
Which is 22 hours later.
Basically RT is getting its UTC and Australia/Melbourne all askew.
RT says "12:14, that would be UTC, better convert it to
Australia/Melbourne by subtracting 11 hours. That would be 1:14" and
sends that to the database.
Database says "1:14, that would be UTC, better show Scott the local
equivelent and subtract 11 hours."
Whereas the conversation SHOULD be:
RT says "12:14, that would be Australia/Melbourne, better convert it
to UTC by adding 11 hours. That would be 23:14" and sends that to the
database.
Database says "23:14, that would be UTC, better show Scott the local
equivelent and subtract 11 hours."
And you get 12:14, which is the value you want.
As for how to fix it, it's a bug that RT should fix. The other option
is to force the database to use the UTC timezone and then think of
all the results as being localtime :/
Cheers!
Rick
More information about the Melbourne-pm
mailing list