CONVERT_TZ 在冬季/夏季时间更改时间无法正常工作 [英] CONVERT_TZ not working properly during winter/summer time change hour

问题描述

在巴黎(欧洲时区/巴黎)，时区偏移更改于 2016 年 3 月 27 日凌晨 2:00.时区在 UTC+1 之前和之后在 UTC+2.

In Paris (timezone Europe/Paris) the timezone offset change on 2016-03-27 at 2:00am. Timezone is before at UTC+1 and after at UTC+2.

Mysql 函数 CONVERT_TZ 不工作，如果你在更改期间给出时间:在 2016-03-27 02:00:01 和 2016-03-27 02:59:59 之间

Mysql function CONVERT_TZ not work if you give a time during the change : between 2016-03-27 02:00:01 and 2016-03-27 02:59:59

示例:

SELECT CONVERT_TZ('2016-03-27 02:18:29', '欧洲/巴黎', "UTC") === '2016-03-27 01:00:00'

SELECT CONVERT_TZ('2016-03-27 02:18:29', 'Europe/Paris', "UTC" ) === '2016-03-27 01:00:00'

而且很有趣:SELECT CONVERT_TZ('2016-03-27 01:00:00', 'UTC', "欧洲/巴黎" ) === '2016-03-27 03:00:00'

And funny : SELECT CONVERT_TZ('2016-03-27 01:00:00', 'UTC', "Europe/Paris" ) === '2016-03-27 03:00:00'

我必须将日期在多个时区的 mysql 数据库转换为直接在 UTC 时区(安全)中的所有日期的新数据库，此问题某些日期将是错误的 :(

I have to convert a mysql database with date in many timezone to a new database with all date directly in UTC timezone (safe), with this issue some date will be wrong :(

推荐答案

给定一个无效"的本地时间，MySQL 的 CONVERT_TZ 函数会在转换之前将其提前到下一个有效的本地时间.由于您的 2:18:29 值确实没有出现在那个时区的那个日期，所以它被提前到 3:00:00 当地时间，然后正确转换为1:00:00 UTC.

Given an "invalid" local time, MySQL's CONVERT_TZ function advances it to the next valid local time before converting it. Since your 2:18:29 value really does not occur on that date in that time zone, it is advanced to 3:00:00 local time, then converted correctly to 1:00:00 UTC.

在秋季，当夏令时于 2016 年 10 月 30 日在巴黎结束时，2:18:29 将发生两次.你会发现 MySQL 的 CONVERT_TZ 函数选择了两次出现的 second.

In the fall, when DST ends in Paris on October 30th 2016, 2:18:29 will occur twice. You'll find that MySQL's CONVERT_TZ function chooses the second of two occurrences.

这种行为是一致的，因此说它不起作用是不正确的.它有效，只是可能不是您喜欢的行为.

This behavior is consistent, and thus saying it doesn't work is incorrect. It works, it just may not be the behavior you would prefer.

就我个人而言，我认为在春天跳到 3:18:39(转换为 1:18:29 UTC)会更好.我还认为在秋季最好使用两次出现的 第一个.我认为这种行为适用于更多的现实场景，但这只是我的看法.

Personally, I think in the spring it would be better to skip ahead to 3:18:39 (which converts to 1:18:29 UTC). I also think in the fall it's better to use the first of the two occurrences. I think that behavior accommodates a larger number of real-world scenarios, but that's just my opinion.

此外，为什么您的数据中有无效时间?如果它们是从真实事件中记录的，时间将在时钟上跳过，因此不会最终出现在您的数据库中.所以通常情况下，只有在处理计算时间(例如重复计划)时，才需要处理这个问题.

Besides, why do you have invalid times in your data to begin with? If they are recorded from real events, the time will be skipped on the clock and thus not end up in your database. So usually, one only has to deal with this when dealing with calculation of time, such as a recurring schedule.

关于你的第二个例子，那里没有错.当时区偏移为 UTC+2 时，1:00 UTC 在巴黎确实是 3:00.

Regarding your second example, there's nothing wrong there. 1:00 UTC would indeed be 3:00 in Paris when the time zone offset is UTC+2.

这篇关于CONVERT_TZ 在冬季/夏季时间更改时间无法正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！