php MySQL 日期时间字段和夏令时——如何引用“额外”小时？

MySQL datetime fields and daylight savings time -- how do I reference the "extra" hour?

提问by Aaron

I'm using the America/New York timezone. In the Fall we "fall back" an hour -- effectively "gaining" one hour at 2am. At the transition point the following happens:

我正在使用美国/纽约时区。在秋季，我们“退回”一小时——实际上是在凌晨 2 点“增加”一小时。在过渡点发生以下情况：

it's 01:59:00 -04:00
then 1 minute later it becomes:
01:00:00 -05:00

现在是 01:59:00 -04:00
然后 1 分钟后变成：01 :00:
00 -05:00

So if you simply say "1:30am" it's ambiguous as to whether or not you're referring to the first time 1:30 rolls around or the second. I'm trying to save scheduling data to a MySQL database and can't determine how to save the times properly.

因此，如果您只是说“凌晨 1:30”，那么您指的是第一次 1:30 还是第二次出现是模棱两可的。我正在尝试将调度数据保存到 MySQL 数据库，但无法确定如何正确保存时间。

Here's the problem:
"2009-11-01 00:30:00" is stored internally as 2009-11-01 00:30:00 -04:00
"2009-11-01 01:30:00" is stored internally as 2009-11-01 01:30:00 -05:00

问题是：
“2009-11-01 00:30:00”在内部存储为 2009-11-01 00:30:00 -04:00
“2009-11-01 01:30:00”在内部存储为2009-11-01 01:30:00 -05:00

This is fine and fairly expected. But how do I save anything to 01:30:00 -04:00? The documentationdoes not show any support for specifying the offset and, accordingly, when I've tried specifying the offset it's been duly ignored.

这很好，也很符合预期。但是如何将任何内容保存到 01:30:00 -04:00？该文档没有显示对指定偏移量的任何支持，因此，当我尝试指定偏移量时，它被适当地忽略了。

The only solutions I've thought of involve setting the server to a timezone that doesn't use daylight savings time and doing the necessary transformations in my scripts (I'm using PHP for this). But that doesn't seem like it should be necessary.

我想到的唯一解决方案是将服务器设置为不使用夏令时的时区并在我的脚本中进行必要的转换（我为此使用 PHP）。但这似乎没有必要。

Many thanks for any suggestions.

非常感谢您的任何建议。

采纳答案by Steve Clay

MySQL's date types are, frankly, broken and cannot store alltimes correctly unless your system is set to a constant offset timezone, like UTC or GMT-5. (I'm using MySQL 5.0.45)

坦率地说，MySQL 的日期类型已损坏并且无法正确存储所有时间，除非您的系统设置为恒定偏移时区，例如 UTC 或 GMT-5。（我使用的是 MySQL 5.0.45）

This is because you can't store any time during the hour before Daylight Saving Time ends. No matter how you input dates, every date function will treat these times as if they are during the hour after the switch.

这是因为您无法在夏令时结束前的一小时内存储任何时间。无论您如何输入日期，每个日期函数都会将这些时间视为切换后的一小时内。

My system's timezone is America/New_York. Let's try storing 1257051600 (Sun, 01 Nov 2009 06:00:00 +0100).

我系统的时区是America/New_York. 让我们尝试存储 1257051600（Sun, 01 Nov 2009 06:00:00 +0100）。

Here's using the proprietary INTERVAL syntax:

这是使用专有的 INTERVAL 语法：

SELECT UNIX_TIMESTAMP('2009-11-01 00:00:00' + INTERVAL 3599 SECOND); # 1257051599
SELECT UNIX_TIMESTAMP('2009-11-01 00:00:00' + INTERVAL 3600 SECOND); # 1257055200

SELECT UNIX_TIMESTAMP('2009-11-01 01:00:00' - INTERVAL 1 SECOND); # 1257051599
SELECT UNIX_TIMESTAMP('2009-11-01 01:00:00' - INTERVAL 0 SECOND); # 1257055200

Even FROM_UNIXTIME()won't return the accurate time.

甚至FROM_UNIXTIME()不会返回准确的时间。

SELECT UNIX_TIMESTAMP(FROM_UNIXTIME(1257051599)); # 1257051599
SELECT UNIX_TIMESTAMP(FROM_UNIXTIME(1257051600)); # 1257055200

Oddly enough, DATETIME willstill store and return (in string form only!) times within the "lost" hour when DST starts (e.g. 2009-03-08 02:59:59). But using these dates in any MySQL function is risky:

奇怪的是，DATETIME仍然会在 DST 开始时（例如2009-03-08 02:59:59）的“丢失”小时内存储和返回（仅以字符串形式！）时间。但是在任何 MySQL 函数中使用这些日期都是有风险的：

SELECT UNIX_TIMESTAMP('2009-03-08 01:59:59'); # 1236495599
SELECT UNIX_TIMESTAMP('2009-03-08 02:00:00'); # 1236495600
# ...
SELECT UNIX_TIMESTAMP('2009-03-08 02:59:59'); # 1236495600
SELECT UNIX_TIMESTAMP('2009-03-08 03:00:00'); # 1236495600

The takeaway: If you need to store and retrieve everytime in the year, you have a few undesirable options:

要点：如果你需要在一年中的每次存储和检索，你有一些不受欢迎的选择：

1. Set system timezone to GMT + some constant offset. E.g. UTC

2. Store dates as INTs (as Aaron discovered, TIMESTAMP isn't even reliable)

3. Pretend the DATETIME type has some constant offset timezone. E.g. If you're in America/New_York, convert your date to GMT-5 outside of MySQL, then store as a DATETIME (this turns out to be essential: see Aaron's answer). Then you must take great care using MySQL's date/time functions, because some assume your values are of the system timezone, others (esp. time arithmetic functions) are "timezone agnostic" (they may behave as if the times are UTC).

1. 将系统时区设置为 GMT + 一些恒定偏移量。例如 UTC

2. 将日期存储为 INT（正如 Aaron 发现的那样，TIMESTAMP 甚至不可靠）

3. 假设 DATETIME 类型有一些恒定的偏移时区。例如，如果您在 中America/New_York，请将您的日期转换为 MySQL 之外的GMT-5 ，然后存储为 DATETIME（事实证明这是必不可少的：请参阅 Aaron 的回答）。那么你必须非常小心地使用 MySQL 的日期/时间函数，因为有些假设你的值是系统时区的，其他的（尤其是时间算术函数）是“时区不可知的”（它们的行为可能就像时间是 UTC）。

Aaron and I suspect that auto-generating TIMESTAMP columns are also broken. Both 2009-11-01 01:30 -0400and 2009-11-01 01:30 -0500will be stored as the ambiguous 2009-11-01 01:30.

Aaron 和我怀疑自动生成的 TIMESTAMP 列也被破坏了。双方2009-11-01 01:30 -0400并2009-11-01 01:30 -0500会被存储为暧昧2009-11-01 01:30。

回答by Aaron

I've got it figured out for my purposes. I'll summarize what I learned (sorry, these notes are verbose; they're as much for my future referral as anything else).

为了我的目的，我已经弄清楚了。我将总结我学到的东西（对不起，这些笔记很冗长；它们和其他任何东西一样对我未来的推荐有用）。

Contrary to what I said in one of my previous comments, DATETIME and TIMESTAMP fields dobehave differently. TIMESTAMP fields (as the docs indicate) take whatever you send them in "YYYY-MM-DD hh:mm:ss" format and convert it from your current timezone to UTC time. The reverse happens transparently whenever you retrieve the data. DATETIME fields do not make this conversion. They take whatever you send them and just store it directly.

与我在之前的评论之一中所说的相反，DATETIME 和 TIMESTAMP 字段的行为确实不同。TIMESTAMP 字段（如文档所示）采用您以“YYYY-MM-DD hh:mm:ss”格式发送的任何内容，并将其从当前时区转换为 UTC 时间。每当您检索数据时，反向都会透明地发生。DATETIME 字段不进行此转换。他们拿走你发送给他们的任何东西，然后直接存储。

Neither the DATETIME nor the TIMESTAMP field types can accurately store data in a timezone that observes DST. If you store "2009-11-01 01:30:00" the fields have no way to distinguish which version of 1:30am you wanted -- the -04:00 or -05:00 version.

DATETIME 和 TIMESTAMP 字段类型都不能准确地将数据存储在遵守 DST 的时区中。如果您存储“2009-11-01 01:30:00”，这些字段将无法区分您想要哪个版本的 1:30am——-04:00 或 -05:00 版本。

Ok, so we must store our data in a non DST timezone (such as UTC). TIMESTAMP fields are unable to handle this data accurately for reasons I'll explain: if your system is set to a DST timezone then what you put into TIMESTAMP may not be what you get back out. Even if you send it data that you've already converted to UTC, it will still assume the data's in your local timezone and do yet another conversion to UTC. This TIMESTAMP-enforced local-to-UTC-back-to-local roundtrip is lossy when your local timezone observes DST (since "2009-11-01 01:30:00" maps to 2 different possible times).

好的，所以我们必须将数据存储在非 DST 时区（例如 UTC）中。由于我将解释的原因，TIMESTAMP 字段无法准确处理这些数据：如果您的系统设置为 DST 时区，那么您放入 TIMESTAMP 的内容可能不是您返回的内容。即使您向它发送已经转换为 UTC 的数据，它仍会假定数据位于您的本地时区，并再次转换为 UTC。当您的本地时区遵守夏令时（因为“2009-11-01 01:30:00”映射到 2 个不同的可能时间）时，这种 TIMESTAMP 强制执行的本地到 UTC 返回本地往返是有损的。

With DATETIME you can store your data in any timezone you want and be confident that you'll get back whatever you send it (you don't get forced into the lossy roundtrip conversions that TIMESTAMP fields foist on you). So the solution is to use a DATETIME field and before saving to the fieldconvert from your system time zone into whatever non-DST zone you want to save it in (I think UTC is probably the best option). This allows you to build the conversion logic into your scripting language so that you can explicitly save the UTC equivalent of "2009-11-01 01:30:00 -04:00" or ""2009-11-01 01:30:00 -05:00".

使用 DATETIME，您可以将您的数据存储在您想要的任何时区，并确信您会取回您发送的任何内容（您不会被迫进行 TIMESTAMP 字段强加给您的有损往返转换）。因此，解决方案是使用 DATETIME 字段，然后在保存到该字段之前，将系统时区转换为要保存的任何非 DST 区域（我认为 UTC 可能是最佳选择）。这允许您将转换逻辑构建到您的脚本语言中，以便您可以显式保存“2009-11-01 01:30:00 -04:00”或“”2009-11-01 01:30 的 UTC 等效值： 00 -05:00"。

Another important thing to note is that MySQL's date/time math functions don't work properly around DST boundaries if you store your dates in a DST TZ. So all the more reason to save in UTC.

另一个需要注意的重要事项是，如果您将日期存储在 DST TZ 中，MySQL 的日期/时间数学函数将无法在 DST 边界附近正常工作。所以更有理由保存在 UTC 中。

In a nutshell I now do this:

简而言之，我现在这样做：

When retrieving the data from the database:

从数据库中检索数据时：

Explicitly interpret the data from the database as UTC outside of MySQL in order to get an accurate Unix timestamp. I use PHP's strtotime() function or its DateTime class for this. It can not be reliably done inside of MySQL using MySQL's CONVERT_TZ() or UNIX_TIMESTAMP() functions because CONVERT_TZ will only output a 'YYYY-MM-DD hh:mm:ss' value which suffers from ambiguity problems, and UNIX_TIMESTAMP() assumes its input is in the system timezone, not the timezone the data was ACTUALLY stored in (UTC).

将数据库中的数据显式解释为 MySQL 之外的 UTC，以获得准确的 Unix 时间戳。为此，我使用 PHP 的 strtotime() 函数或其 DateTime 类。使用 MySQL 的 CONVERT_TZ() 或 UNIX_TIMESTAMP() 函数无法在 MySQL 内部可靠地完成它，因为 CONVERT_TZ 只会输出一个 'YYYY-MM-DD hh:mm:ss' 值，该值存在歧义问题，而 UNIX_TIMESTAMP() 假定其输入是系统时区，而不是数据实际存储的时区（UTC）。

When storing the data to the database:

将数据存储到数据库时：

Convert your date to the precise UTC time that you desire outside of MySQL. For example: with PHP's DateTime class you can specify "2009-11-01 1:30:00 EST" distinctly from "2009-11-01 1:30:00 EDT", then convert it to UTC and save the correct UTC time to your DATETIME field.

将您的日期转换为您希望在 MySQL 之外的精确 UTC 时间。例如：使用 PHP 的 DateTime 类，您可以将“2009-11-01 1:30:00 EST”与“2009-11-01 1:30:00 EDT”区分开来，然后将其转换为 UTC 并保存正确的 UTC 时间到您的 DATETIME 字段。

Phew. Thanks so much for everyone's input and help. Hopefully this saves someone else some headaches down the road.

呼。非常感谢大家的投入和帮助。希望这可以为其他人节省一些麻烦。

BTW, I am seeing this on MySQL 5.0.22 and 5.0.27

顺便说一句，我在 MySQL 5.0.22 和 5.0.27 上看到了这个

回答by Steve Clay

I think micahwittman's linkhas the best practical solution to these MySQL limitations: Set the session timezone to UTC when you connect:

我认为 micahwittman 的链接具有针对这些 MySQL 限制的最佳实用解决方案：连接时将会话时区设置为 UTC：

SET SESSION time_zone = '+0:00'

Then you just send it Unix timestamps and everything should be fine.

然后你只需向它发送 Unix 时间戳，一切都应该没问题。

回答by Manuel Darveau

This thread made me freak since we use TIMESTAMPcolumns with On UPDATE CURRENT_TIMESTAMP(ie: recordTimestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) to track changed records and ETL to a datawarehouse.

这个线程让我感到很奇怪，因为我们使用TIMESTAMP带有On UPDATE CURRENT_TIMESTAMP(ie: recordTimestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) 的列来跟踪更改的记录和 ETL 到数据仓库。

In case someone wonder, in this case, TIMESTAMPbehave correctly and you can differentiate between the two similar dates by converting the TIMESTAMPto unix timestamp:

如果有人想知道，在这种情况下，TIMESTAMP行为是否正确，您可以通过将 转换TIMESTAMP为 unix 时间戳来区分两个相似的日期：

select TestFact.*, UNIX_TIMESTAMP(recordTimestamp) from TestFact;

id  recordTimestamp         UNIX_TIMESTAMP(recordTimestamp)
1   2012-11-04 01:00:10.0   1352005210
2   2012-11-04 01:00:10.0   1352008810

回答by Andomar

But how do I save anything to 01:30:00 -04:00?

但是如何将任何内容保存到 01:30:00 -04:00？

You can convert to UTC like:

您可以转换为 UTC，如：

SELECT CONVERT_TZ('2009-11-29 01:30:00','-04:00','+00:00');

---

---

更好的是，将日期保存为 TIMESTAMP时间戳字段。它总是以 UTC 存储，而 UTC 不知道夏/冬时间。

You can convert from UTC to localtime using CONVERT_TZ:

您可以使用CONVERT_TZ从 UTC 转换为本地时间：

SELECT CONVERT_TZ(UTC_TIMESTAMP(),'+00:00','SYSTEM');

Where '+00:00' is UTC, the from timezone , and 'SYSTEM' is the local timezone of the OS where MySQL runs.

其中 '+00:00' 是 UTC，来自时区，'SYSTEM' 是运行 MySQL 的操作系统的本地时区。

回答by Arpan Jain

Mysql inherently solves this problem using time_zone_name table from mysql db. Use CONVERT_TZ while CRUD to update the datetime without worrying about daylight savings time.

Mysql 使用 mysql db 中的 time_zone_name 表固有地解决了这个问题。在 CRUD 时使用 CONVERT_TZ 来更新日期时间，而不必担心夏令时。

SELECT
  CONVERT_TZ('2019-04-01 00:00:00','Europe/London','UTC') AS time1,
  CONVERT_TZ('2019-03-01 00:00:00','Europe/London','UTC') AS time2;

回答by Lukas

I was working on logging counts of visits of pages and displaying the counts in graph (using Flot jQuery plugin). I filled the table with test data and everything looked fine, but I noticed that at the end of the graph the points were one day off according to labels on x-axis. After examination I noticed that the view count for day 2015-10-25 was retrieved twice from the database and passed to Flot, so every day after this date was moved by one day to right.
After looking for a bug in my code for a while I realized that this date is when the DST takes place. Then I came to this SO page...
...but the suggested solutions was an overkill for what I needed or they had other disadvantages. I am not very worried about not being able to distinguish between ambiguous timestamps.I just need to count and display records per days.

我正在记录页面访问次数并在图表中显示计数（使用 Flot jQuery 插件）。我用测试数据填满了表格，一切看起来都很好，但我注意到在图表的末尾，根据 x 轴上的标签，点是一天。经过检查，我注意到 2015-10-25 天的查看计数从数据库中检索了两次并传递给 Flot，因此该日期之后的每一天都向右移动一天。
在我的代码中寻找错误一段时间后，我意识到这个日期是 DST 发生的日期。然后我来到了这个 SO 页面......
但建议的解决方案对于我需要的东西来说太过分了，或者他们有其他缺点。我不是很担心无法区分不明确的时间戳。我只需要计算和显示每天的记录。

First, I retrieve the date range:

首先，我检索日期范围：

SELECT 
    DATE(MIN(created_timestamp)) AS min_date, 
    DATE(MAX(created_timestamp)) AS max_date 
FROM page_display_log
WHERE item_id = :item_id

Then, in a for loop, starting with min_date, ending with max_date, by step of one day (60*60*24), I'm retrieving the counts:

然后，在 for 循环中，以 开始min_date，以 结束max_date，以一天为单位（60*60*24），我正在检索计数：

for( $day = $min_date_timestamp; $day <= $max_date_timestamp; $day += 60 * 60 * 24 ) {
    $query = "
        SELECT COUNT(*) AS count_per_day
        FROM page_display_log
        WHERE 
            item_id = :item_id AND
            ( 
                created_timestamp BETWEEN 
                '" . date( "Y-m-d 00:00:00", $day ) . "' AND
                '" . date( "Y-m-d 23:59:59", $day ) . "'
            )
    ";
    //execute query and do stuff with the result
}

My final and quick solutionto myproblem was this:

我对我的问题的最终和快速解决方案是这样的：

$min_date_timestamp += 60 * 60 * 2; // To avoid DST problems
for( $day = $min_date_timestamp; $day <= $max_da.....

So I am not staring the loop in the beginning of the day, but two hours later. The day is still the same, and I am still retrieving correct counts, since I explicitly ask the database for records between 00:00:00 and 23:59:59 of the day, regardless of the actual time of the timestamp. And when the time jumps by one hour, I am still in the correct day.

所以我不是在一天开始的时候盯着循环，而是在两个小时后。这一天还是一样，我仍然在检索正确的计数，因为我明确地要求数据库提供当天 00:00:00 和 23:59:59 之间的记录，而不管时间戳的实际时间。当时间跳跃一小时时，我仍然在正确的一天。

Note: I know this is 5 year old thread, and I know this is not an answer to OPs question, but it might help people like me who encountered this page looking for solution to the problem I described.

注意：我知道这是 5 年前的帖子，我知道这不是 OP 问题的答案，但它可能会帮助像我这样遇到此页面的人寻找我描述的问题的解决方案。