MySQL 1292 日期时间值不正确
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/35602939/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL 1292 Incorrect datetime value
提问by CycleGeek
I am getting this error when I try to insert '2011/03/13 02:53:50.000000000' into a timestamp column. If I change the 13 to a 15, 14, 12 or 11 it works no problem. I've also tried changing the /'s to -'s and still no-go.
当我尝试将 '2011/03/13 02:53:50.000000000' 插入时间戳列时出现此错误。如果我将 13 更改为 15、14、12 或 11，它就没有问题。我也试过将 / 更改为 - 并且仍然不行。
I've looked through some of the other threads related to this error but none seem to apply.
我已经查看了与此错误相关的其他一些线程，但似乎没有一个适用。
I'm running version 5.7.9.
我正在运行 5.7.9 版。
回答by nik
It took me a while to figure this out...
我花了一段时间才弄清楚这一点......
The problem is that '2011-03-13 02:53:50' is illegal because of daylight saving time switch between 2 and 3 AM, so all time values between 2 and 3 am on any DST introduction day are invalid. Same for '2016-03-13 02:32:21', etc.
问题是“2011-03-13 02:53:50”是非法的，因为夏令时在凌晨 2 点到 3 点之间切换，因此在任何 DST 引入日凌晨 2 点到 3 点之间的所有时间值都是无效的。'2016-03-13 02:32:21' 等也是如此。
Change the system timezone to the one that does not use DST and you should be fine.
将系统时区更改为不使用 DST 的时区，您应该没问题。
回答by Rahul Tripathi
You need to try this:
你需要试试这个：
STR_TO_DATE( '2011/03/13 02:53:50', '%Y/%m/%d %H:%i:%s')
or else you have to insert the dates using the dash seperator (-)like
否则，您必须使用破折号分隔符(-)插入日期，例如
'2011-03-13 02:53:50'
回答by Farside
I think you need to use some str conversions in MySQLbefore inserting. Or to prepare the data in the proper format, before making the query to MySQL.
我认为您需要在插入之前在 MySQL 中使用一些str 转换。或者在对 MySQL 进行查询之前以正确的格式准备数据。
The microseconds format is also wrong. MySQL documentation clearly states this:
微秒格式也是错误的。MySQL 文档清楚地说明了这一点：
A DATETIME or TIMESTAMP value can include a trailing fractional seconds partin up to microseconds (6 digits)precision.
DATETIME 或 TIMESTAMP 值可以包括尾随小数秒部分，精度最高可达微秒（6 位）。
UPDATE: on my localhost I've got the same version of MySQL, and it works. Tryed to execute conversion
更新：在我的本地主机上，我有相同版本的 MySQL，它可以工作。尝试执行转换
select str_to_date("2011-03-13 02:53:50.000000", "%Y-%m-%d %H:%i:%s.%f") as `t`
and gotten:
并得到：
+----------------------------+
| t |
+----------------------------+
| 2011-03-13 02:53:50.000000 |
+----------------------------+
1 row in set (0.00 sec)
Here's the SQLFiddle, that confirms the thing on other version of MySQL.
这是SQLFiddle，它确认了其他版本的 MySQL 上的事情。
I run out of ideas, I think the issue is connected to the "local glitch" in Table structure or specific version of MySQL+OS.
我的想法用完了，我认为问题与表结构或特定版本的 MySQL+OS 中的“本地故障”有关。
回答by CycleGeek
Still not sure what the issue is/was, maybe a combination of CentOS and MySQL versions. I changed the column to datatime(6) instead of timestamp(6) and I was able to import all my data successfully.
仍然不确定问题是/曾经是什么，可能是 CentOS 和 MySQL 版本的组合。我将列更改为 datatime(6) 而不是 timestamp(6) 并且我能够成功导入我的所有数据。
