RT (Request Tracker) Date Handling

[Scott Penrose]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I have confirmed that it is all RT's fault.

There are a number of ways of entering dates into a database (in this 
case mysql). If you enter a date directly simply (eg: "2003-11-01 
23:14") then it assumes it is the local time which is setup on the 
system (this default behaviour can be changed, but normally it SHOULD 
be this way).

However, you can also tell it the timezone, or even the GMT or even the 
Unix Time Stamp. All of these work perfectly in mysql - as they should.

All (sensible) databases store the dates normalised in GMT format and 
store the offset, so that it can be asked back in any format you want.

RT however expects that the default behaviour of a database be changed 
to accept strings as assumed to be in GMT. It then converts them back 
on output.

For those developing databases you should do either read/write to the 
database in GMT time and convert, or let the database do it for  you - 
not both. Mysql has some great methods for asking the time in the 
format you want.

We have a solution we are about to try which is to tell the mysql 
database that it always receives and should return dates in GMT unless 
specifically asked - although this will solve my problem - it means 
that everyone with a spreadsheet or any other means of doing the query 
will be getting GMT - and have to convert every date (not an easy task 
in Excel etc). My code fortunately will be unaffected as it always only 
deals with unix time stamps and converts only on output - as I don't 
care at this stage about time zone. The problem is of course that all 
the dates are probably going to come out wrong out of the database.

Anyway - this is all just an FYI so that future development of date 
handling in databases is done properly :-)

Scooter
- -- 
Scott Penrose
Anthropomorphic Personification Expert
http://search.cpan.org/search?author=SCOTT
scott at cpan.org

Dismaimer: While every attempt has been made to make sure that this 
email only contains zeros and ones, there has been no effort made to 
guarantee the quantity or the order.

Please do not send me Word or PowerPoint attachments.
See http://www.fsf.org/philosophy/no-word-attachments.html 
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (Darwin)
Comment: For info see http://www.gnupg.org

iD8DBQE/rXxcDCFCcmAm26YRAhRmAJwM27ZdBCdi1FceSkavBrdPLRZTEACfaHrm
J4qRtO+0yVBSxy28/aKBahY=
=Ed2X
-----END PGP SIGNATURE-----