mysql datetime字段和夏令时-如何引用“额外”小时?

坦率地说,MySQL的日期类型已损坏,无法存储 全部的 正确的时间,除非您的系统设置为一个固定的偏移时区,如UTC或GMT-5。(我使用的是MySQL5.0.45)

这是因为 在夏令时结束前一小时内,您不能存储任何时间 . 无论您如何输入日期,每个日期函数都会将这些时间视为开关后一小时内的时间。

我的系统的时区是 America/New_York . 让我们尝试存储1257051600(Sun,2009年11月1日06:00:00+0100)。

下面使用专有的间隔语法:

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

偶数 FROM_UNIXTIME() 不会返回准确的时间。

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

奇怪的是,日期时间 将 仍然存储并返回(仅以字符串形式!)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

外卖:如果你需要储存和取回 每一个 一年中的某个时候,你有一些不受欢迎的选择:

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

2. 将日期存储为整数(正如Aaron发现的那样,时间戳甚至不可靠)

3. 假设日期时间类型具有某个常量偏移时区。例如,如果你在 美洲/纽约 ,将日期转换为GMT-5 MySQL外部 ,然后存储为日期时间(这很重要:请参阅Aaron的答案)。那么,您必须非常注意使用MySQL的日期/时间函数,因为有些函数假定您的值是系统时区的,而另一些函数(尤其是时间算术函数)则是“时区不可知论”(它们的行为可能与时间是UTC类似)。

Aaron和我怀疑自动生成时间戳列也被破坏了。两个 2009-11-01 01:30 -0400 和 2009-11-01 01:30 -0500 将存储为不明确的 2009-11-01 01:30 .

为了我的目的,我已经弄清楚了。我来总结一下我学到的东西(对不起,这些笔记很冗长;它们对我以后的推荐和其他任何东西一样重要)。

与我在以前的某个注释中所说的相反,日期时间和时间戳字段 做 表现不同。时间戳字段(如文档所示)以“yyyy-mm-dd hh:mm:ss”格式接收您发送的任何内容,并将其从当前时区转换为UTC时间。当您检索数据时,就会透明地执行相反的操作。日期时间字段不进行此转换。他们拿走你寄给他们的任何东西,直接储存起来。

日期时间和时间戳字段类型都不能在观察DST的时区中准确存储数据。 .如果您存储“2009-11-01 01:30:00”,字段将无法区分您想要的1:30版本,即-04:00或-05:00版本。

好的,所以我们必须将数据存储在非DST时区(如UTC)。时间戳字段无法准确处理这些数据,原因是我将解释:如果您的系统设置为DST时区,那么您放入时间戳的内容可能不会是您返回的内容。即使您向IT发送已经转换为UTC的数据,它仍然会假设数据在您的本地时区中,并再次转换为UTC。当您的本地时区观察到DST时,此时间戳强制从本地到UTC返回本地往返是有损的(从“2009-11-01 01:30:00”映射到两个不同的可能时间)。

使用datetime,您可以将数据存储在您想要的任何时区,并且确信您可以返回发送的任何数据(您不会被迫进行有损的往返转换,因为时间戳字段会影响您)。所以解决方案是使用日期时间字段和 在保存到字段之前 将系统时区转换为要保存它的任何非DST区域(我认为UTC可能是最佳选择)。这允许您将转换逻辑构建到脚本语言中,以便显式保存相当于“2009-11-01 01:30:00-04:00”或“2009-11-01 01:30:00-05:00”的UTC。

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

简言之,我现在这样做:

从数据库检索数据时:

显式地将数据库中的数据解释为MySQL之外的UTC,以便获得准确的Unix时间戳。为此,我使用了php的strtotime()函数或其datetime类。使用mysql的convert_tz()或unix_timestamp()函数在mysql内部无法可靠地完成这项工作,因为convert_tz只会输出一个“yyyy-mm-dd hh:mm:s s”值,该值存在歧义问题,unix_timestamp()假定其输入位于系统时区,而不是实际存储数据的时区(UTC)。

将数据存储到数据库时:

将日期转换为除MySQL之外所需的精确的UTC时间。例如:使用php的datetime类,可以将“2009-11-01 1:30:00 est”与“2009-11-01 1:30:00 edt”区分开来,然后将其转换为UTC,并将正确的UTC时间保存到datetime字段中。

呸。非常感谢大家的投入和帮助。希望这能让别人省去路上的头痛。

顺便说一下,我在MySQL5.0.22和5.0.27上看到了这个。

我想是麦卡维特曼的 link 对于这些MySQL限制有最佳的实际解决方案:连接时将会话时区设置为UTC:

SET SESSION time_zone = '+0:00'

然后您只需发送它Unix时间戳,一切都应该正常。

但是我怎么把东西存到01:30:00呢 04:00?

您可以转换为UTC,例如:

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

您可以使用 CONVERT_TZ :

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

其中“+00:00”是UTC,而“系统”是运行MySQL的操作系统的本地时区。

这条线让我很奇怪,因为我们用 TIMESTAMP 带柱 On UPDATE CURRENT_TIMESTAMP (I: recordTimestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP )跟踪更改的记录和ETL到数据仓库。

如果有人想知道,在这种情况下, 时间戳 行为正确,您可以通过转换 时间戳 到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

我正在记录访问页面的次数,并在图形中显示这些次数(使用flot jquery插件)。我用测试数据填满了表格,一切看起来都很好,但我注意到在图表的末尾,根据X轴上的标签,这些点有一天是空的。在检查之后,我注意到2015-10-25天的视图计数从数据库中检索了两次,并传递给了Flot,所以在这个日期之后的每一天都被右移一天。
在我的代码中查找了一段时间后,我意识到这个日期是DST发生的时间。然后我来到这个页面…
…但建议的解决方案对我需要的东西来说是多余的,或者它们有其他缺点。 我并不担心无法区分模糊的时间戳。 我只需要每天统计和显示记录。

首先,我检索日期范围:

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

然后,在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
}

我的 最终快速解决方案 到 我的 问题是:

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

所以我是 不是一天开始就盯着圈看,而是两个小时后 . 这一天仍然是相同的,我仍然在检索正确的计数,因为我明确地向数据库询问当天00:00:00到23:59:59之间的记录,而不管时间戳的实际时间如何。当时间增加一小时,我仍然是正确的一天。

注:我知道这是一个5年前的线索,我知道这不是一个答案的运作问题,但它可能有助于像我这样的人遇到这个页面寻找解决方案,我描述的问题。