Yesterday Europe switched the daylight saving time. Though for most people thats a great change (you can sleep one hour longer, you’re not late for one day in whole year, etc), thats a data nightmare…
- Same time happens twice
- Time is not sequential value
- Criteria for time gets fuzzy
So, if you are using local time, instead of UTC, your data may hit unique constraints (if anyone has unique constraints on datetimes, of course ;-), or simply you won’t be able to detect which moment of time it refers to (bad for audit trails!)
The fun part within MySQL is that DATETIME is time-zone agnostic (it just stores presentation values), whereas TIMESTAMP is not (it stores data in UTC and presents it based on session timezone), so the behaviors for these will be different.
How different? DATETIME will just have non-sequential lossy information, and TIMESTAMP will have… depends. If implicit TIMESTAMP default values are used, it will have correct UTC-based data. If NOW() or literals are inserted, that will provide with lossy presentation-time based values. In order for database to actually understand what literal value means, it would ned an offset included (“hh:mm:ss+hhmm”) in the string (though MySQL currently does not support this notation). NOW() behavior is probably a bug.
Storing time correctly opens another can of worms – user-provided time criteria cannot be converted into UTC, and therefore a lossy match is done. To complicate things even more, as indexes are in sequential time, but data matching is done based on non-sequential time, data returned will be different based on data access method (see Bug#38455).
So, the elegant fix for timezone support inside MySQL would be extending the presentation/literal format to support specified offsets, fixing datetime/timestamp code in multiple places – and that would quite some work to fix one hour per year.
Timezone support is my old interest, there’s lots of complexity and different implications, but most of people just don’t get to that – and may lose their data consistency.
So, how did your Sunday go?
domas mituzas 
Since years, I am telling myself. Don’t set your servers to anything but UTC, no matter wherever they reside, and then use UTC to store time. For user presentation convert it to whatever the user wants to see.
Or, there’s the drizzle approach – only UTC is stored. So you can’t get yourself into heaps of trouble in the database (your app can still suck though).
So far, no issues reported, however, I’m ABSOLUTELY sure some data will be shuffled when we change over here in the US. Would love to use UTC, but sadly, it is not to be so.
A very interesting post! Never thought of this myself :] My sunday was ok. We now have a trail of messed data! \o/
Daylight saving shifts are big pain in one place when you think about software. There are really many ways to introduce bugs in this area and usually you are not able to test them (or you even don’t think about it) before these bugs come to you. In the ideal world you would use something like UTC, Epoch time or always (i mean, always) keep an eye on timezone, but the world is not ideal yet.
P.S. Sunday was quite fine. There was nothing that cannot be fixed on Monday :)