MySQL日期时间字段和夏时制-如何引用“额外”小时?

2020/10/12 10:21 · php ·  · 0评论

我正在使用美国/纽约时区。在秋季,我们“退后”一个小时-有效地在凌晨2点“获得”一个小时。在过渡点发生以下情况:

它是01:59:00 -04:00,

然后一分钟后变成:01
:00 :


00
-05:00

因此,如果您只是简单地说“ 1:30 am”,那么您是指第一次还是1:30滚动还是第二次就不清楚了。我正在尝试将计划数据保存到MySQL数据库,并且无法确定如何正确保存时间。

这是问题所在:

“ 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

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

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

非常感谢您的任何建议。

坦率地说,MySQL的日期类型已损坏,无法正确存储所有时间,除非将系统设置为恒定的偏移时区,例如UTC或GMT-5。(我正在使用MySQL 5.0.45)

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

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

这里使用专有的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

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

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

奇怪的是,DSTTIME仍将在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. 将日期存储为INT(如Aaron发现的,TIMESTAMP甚至不可靠)

  3. 假设DATETIME类型具有一些恒定的偏移时区。例如,如果您进入America/New_York,请将日期转换为MySQL之外的GMT-5 ,然后将其存储为DATETIME(事实证明这是必不可少的:请参阅Aaron的回答)。然后,您必须格外小心地使用MySQL的日期/时间函数,因为某些函数假设您的值是系统时区,而其他函数(尤其是时间算术函数)则是“时区不可知的”(它们的行为好像是UTC一样)。

亚伦和我怀疑自动生成的TIMESTAMP列也已损坏。两者2009-11-01 01:30 -04002009-11-01 01:30 -0500将被存储为模棱两可2009-11-01 01:30

我已经为我的目的弄清楚了。我将总结一下我学到的东西(对不起,这些说明很冗长;它们对我将来的推荐一样重要。)

相反的是我在我以前的意见一说,DATETIME和TIMESTAMP领域表现不同。TIMESTAMP字段(如文档所示)采用您以“ YYYY-MM-DD hh:mm:ss”格式发送的内容,并将其从当前时区转换为UTC时间。每当您检索数据时,透明地进行相反操作。DATETIME字段不进行此转换。他们接受您发送给他们的任何东西,然后直接存储。

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

好的,因此我们必须将数据存储在非DST时区(例如UTC)中。出于以下原因,TIMESTAMP字段无法正确处理此数据:如果您的系统设置为DST时区,那么您放入TIMESTAMP中的内容可能就不会回来。即使您发送已转换为UTC的数据,它仍将假定该数据位于您当地的时区,并再次转换为UTC。当您的本地时区遵守DST时,此TIMESTAMP强制的从本地到UTC到本地到本地的往返行程是有损的(因为“ 2009-11-01 01:30:00”映射到2个不同的可能时间)。

使用DATETIME,您可以将数据存储在所需的任何时区中,并有信心将获得任何发送的数据(不会强迫您将TIMESTAMP字段强制执行的有损往返转换)。因此,解决方案是使用DATETIME字段,然后将其保存到该字段之前,将其从系统时区转换为要保存在其中的任何非DST时区(我认为UTC可能是最佳选择)。这使您可以将转换逻辑构建到脚本语言中,以便可以显式保存UTC等效项,例如“ 2009-11-01 01:30:00 -04:00”或“ 2009-11-01 01:30”: 00 -05:00”。

另一个要注意的重要事项是,如果将日期存储在DST TZ中,则MySQL的日期/时间数学函数在DST边界附近无法正常工作。因此,更需要保存UTC的原因。

简而言之,我现在这样做:

从数据库检索数据时:

明确将数据库中的数据解释为MySQL之外的UTC,以获取准确的Unix时间戳。我为此使用PHP的strtotime()函数或其DateTime类。无法使用MySQL的CONVERT_TZ()或UNIX_TIMESTAMP()函数在MySQL内部可靠地完成此操作,因为CONVERT_TZ仅会输出存在模糊性问题的'YYYY-MM-DD hh:mm:ss'值,并且UNIX_TIMESTAMP()假定其值为输入是在系统时区中,而不是在数据实际存储在(UTC)中的时区。

将数据存储到数据库时:

将您的日期转换为您希望在MySQL以外的确切UTC时间。例如:使用PHP的DateTime类,可以与“ 2009-11-01 1:30:00 EDT”不同地指定“ 2009-11-01 1:30:00 EST”,然后将其转换为UTC并保存正确的UTC时间到您的DATETIME字段。

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

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

我认为micahwittman的链接针对这些MySQL限制提供了最佳的实际解决方案:在连接时将会话时区设置为UTC:

SET SESSION time_zone = '+0:00'

然后,您只需将其发送给Unix时间戳即可,一切都会正常。

由于我们使用TIMESTAMP带有On UPDATE CURRENT_TIMESTAMP(即:)的recordTimestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP来跟踪更改的记录和ETL到数据仓库,所以这个线程让我感到异常

如果有人怀疑这种情况下的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

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

您可以像这样转换为UTC:

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



更好的是,将日期另存为
TIMESTAMP字段。它始终存储在UTC中,并且UTC不了解夏/冬时间。

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

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

其中“ +00:00”是UTC,从time时区开始,“ SYSTEM”是运行MySQL的操作系统的本地时区。

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;

我正在记录访问页面的计数并在图表中显示计数(使用Flot jQuery插件)。我在表中填入了测试数据,一切看起来都很好,但是我注意到,在图形的末尾,根据x轴上的标签,点数减少了一天。检查后,我注意到从数据库中检索了2015-10-25日的视图计数两次,并将其传递给Flot,因此此日期之后的每一天都向右移动了一天。

在我的代码中查找了一段时间的错误之后,我意识到这是DST发生的日期。
然后,我进入了该SO页...


……但是建议的解决方案对于我所需的解决方案来说是过大的,否则它们还有其他缺点。
我不是很担心无法区分模棱两可的时间戳。 我只需要每天统计和显示记录。

首先,我检索日期范围:

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年历史的线程,并且我不知道这不是OP的问题的答案,但是它可能会帮助像我一样遇到此页面的人,为我描述的问题提供解决方案。

本文地址:http://php.askforanswer.com/mysqlriqishijianziduanhexiashizhi-ruheyinyongewaixiaoshi.html
文章标签: ,   ,   ,   ,  
版权声明:本文为原创文章,版权归 admin 所有,欢迎分享本文,转载请保留出处!

文件下载

老薛主机终身7折优惠码boke112

上一篇:
下一篇:

评论已关闭!