RT (Request Tracker) Date Handling
scottp at dd.com.au
Sat Nov 8 17:29:29 CST 2003
-----BEGIN PGP SIGNED MESSAGE-----
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
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 :-)
Anthropomorphic Personification Expert
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.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (Darwin)
Comment: For info see http://www.gnupg.org
-----END PGP SIGNATURE-----
More information about the Melbourne-pm