> There's three common ways to store timestamps in MySQL and they all stink
I used to think that, but now I realize those problems would disappear if you set server time to UTC and only work with UTC values. It's not an excuse for MySQL's absolutely flawed approach to datetime values, but it's a pretty sane workaround.
- it doesn't matter that DATETIME lacks timezone information, since you implicitly treat all timezones as UTC.
- TIMESTAMP would add still the timezone offset, but it wouldn't do anything since the timezone is UTC +00.
With server timezone set to UTC, TIMESTAMP functions identically to DATETIME but with massively reduced capacity, so I don't see any reason to use TIMESTAMP anymore. Compared to BIGINT unix epoch, DATETIME has the advantage of being compatible with MySQL's datetime-manipulating functions (such as ON UPDATE CURRENT_TIMESTAMP).
Explicit timezone is information that cannot be resurrected once lost. “Select all employees around the world who left after 18:00 too often to advise them to care more of their health”. Of course in practice most employers could not care less, so most time libraries feel free to lose it, and developers are aware of that.
You don't drop the timezone information. You manually normalize the timezone to UTC time, application side, before storing it in the database. This is to get around MySQL's crap handling of datetime types:
- TIMESTAMP 'stores' timezone, but it has a limit of year 2038.
- DATETIME has a limit of year 9999, but it does not store timezone at all.
There really is no better way. Pick DATETIME for its higher capacity, then only ever use UTC time in the database to get around the lack of timezone storage in DATETIME.
> There really is no better way. Pick DATETIME for its higher capacity, then only ever use UTC time in the database to get around the lack of timezone storage in DATETIME.
From what I understand, this would mean that you would always have to set the value of datetime columns manually (with a UTC-adjusted value) instead of being able to use 'ON UPDATE current_timestamp'.
Of course, that's unless you set the server time to use UTC (as you suggested above), which strikes me as an extreme and not-very-portable approach.
I used to think that, but now I realize those problems would disappear if you set server time to UTC and only work with UTC values. It's not an excuse for MySQL's absolutely flawed approach to datetime values, but it's a pretty sane workaround.
- it doesn't matter that DATETIME lacks timezone information, since you implicitly treat all timezones as UTC.
- TIMESTAMP would add still the timezone offset, but it wouldn't do anything since the timezone is UTC +00.
With server timezone set to UTC, TIMESTAMP functions identically to DATETIME but with massively reduced capacity, so I don't see any reason to use TIMESTAMP anymore. Compared to BIGINT unix epoch, DATETIME has the advantage of being compatible with MySQL's datetime-manipulating functions (such as ON UPDATE CURRENT_TIMESTAMP).