开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜