Handling timezones in MySQL without zoneinfo
There seems to be no way to get MySQL to return formatted times from timestamps without zoneinfo being imported.
If I use
SET time_zone = '-4:00'
and I create a timestamp for 6:00PM Nov 1, when daylight saving time passes, and the timezone changes to -5:00, that event will no longer be displayed at the correct time.
Is it possible to load timezone data from the application? Or do I need to get all of my timestamps as UNIX timestamps and convert them in the application? If so, that would prevent me from properly sorting dates that don't have times as 12:00AM regardless of the timezone in MySQL.
For clarification of that last point, I have a date column that is converted to a timestamp when the timestamp is nul开发者_如何学Gol. It looks something like:
select my_date, my_timestamp
from my_table
where if(my_timestamp, my_timestamp, timestamp(my_date))
To clarify for the comment: This is stored as timestamps and converted to local time. Multiple events can be returned which span the DST change. I ended up converting all of the times in PHP, which is inconvenient as it requires PHP 5.1 or greater.
精彩评论