Deciding on date/time storage

Databases
1

When I inherited the code base I’m working on, everything was using unix timestamps, and all the converting that goes along with that. I do quite a lot of direct database reading/manipulation, as well as scripting, so it became convenient for me to start using datetime or timestamp formats, as they’re human readable.

Now, I’m starting to doubt this decision. I read this useful-if-inconclusive sitepoint article on date formats: http://www.sitepoint.com/whats-the-best-date-format/

I agree with a lot of the points. ie that a date time should be human readable (which a uts is not), and also that it should contain timezone information (which datetime does not).

Now, I’m not that bothered about internationalisation, but time zones do become an issue with daylight saving time. Ie when the clocks go back, the datetime 2011-10-30 01:30 does not tell you if this was the 1:30 before the clocks when back, or after. So don’t the datetime/timestamp types miss a vital piece of information?

Am I missing something here? Or am I asking for too much out of a format?

2

the issue with the daylight savings time, as well as with timezone offset, is easily resolved by simply storing datetimes in GMT

this means converting on the way in, if your users are in different timezones, and of course converting on the way out again, too

how does storing unix epoch seconds as integers solve those problems? still gotta do conversion on the way in, and on the way out, right? i mean, in addition to converting to/from integer

3

hmmm. You mean like this?

SELECT CONVERT_TZ(NOW(),@@global.time_zone,'GMT');

Sure, I guess that would work. (not sure if I’ve used the correct system variable, though).

Sure, you’ve still got to convert, but at least it’s possible to retrieve from epoch seconds, the correct datetime, taking into account daylight saving. In my above example 2011-10-30 01:30 could actually be one of two times (as in the UK the clocks will have gone back an hour at 2am), but 1319938200 is unambiguous.
However, storing everything as GMT would, indeed, solve the problem, albeit it might require a slight change of mindset!
Cheers