在没有 zoneinfo 的情况下处理 MySQL 中的时区

发布于 2024-10-01 16:02:04 字数 573 浏览 2 评论 0原文

似乎没有办法让 MySQL 在不导入 zoneinfo 的情况下从时间戳返回格式化时间。

如果我使用

SET time_zone = '-4:00'

并创建 11 月 1 日下午 6:00 的时间戳，当夏令时过去且时区更改为 -5:00 时，该事件将不再在正确的时间显示。

是否可以从应用程序加载时区数据？或者我是否需要将所有时间戳获取为 UNIX 时间戳并在应用程序中将它们转换？如果是这样，那么无论 MySQL 中的时区如何，这都会阻止我正确排序没有 12:00AM 时间的日期。

为了澄清最后一点，我有一个日期列，当时间戳为空时，该列会转换为时间戳。它看起来像：

select my_date, my_timestamp
  from my_table
  where if(my_timestamp, my_timestamp, timestamp(my_date))

为了澄清评论：这被存储为时间戳并转换为本地时间。可以返回跨 DST 更改的多个事件。我最终将所有时间都转换为 PHP，这很不方便，因为它需要 PHP 5.1 或更高版本。

原文

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 null. 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.

分享到QQ

分享到微博