RT (Request Tracker) Date Handling

[Rick Measham]

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