MySQL不正确的日期时间值错误的有效日期 [英] MySQL incorrect datetime value error for valid dates

问题描述

我有一个timestamp列，当我尝试输入一些特定的日期和时间时出现错误.

I have a timestamp column and I get an errors when I try to enter some specific dates and times.

例如 2013-03-31 02:13:11 和 2014-03-31 02:55:00 有效，但 2013-03-31 02:55:00 说:

SQL Error (1292): Incorrect datetime value

可能是什么问题?

推荐答案

这可能是夏令时问题，尤其是当您提到引起问题的日期是 2013-03-31 02:55:00 ...大多数欧洲国家/地区开始于2013年开始观测DST的日期.中欧时间在凌晨2点提前了一个小时，这意味着该日期上没有 02:55:00 天！

This could be a daylight saving time issue, especially when you mention that the date causing problem is 2013-03-31 02:55:00... the date on which most European countries started observing DST for the year 2013. Central Europe Time was advanced by one hour at 2AM which means there was no 02:55:00 on that day!

请注意，MySQL将 TIMESTAMP 值从当前时区转换为UTC进行存储，这是引发错误的地方:

Note that MySQL converts TIMESTAMP values from the current time zone to UTC for storage and this is where the error is thrown:

SET time_zone = 'CET';
-- Central Europe Time in 2013: DST starts at 2am when clocks move forward to 3am
-- see https://www.timeanddate.com/news/time/europe-starts-dst-2013.html

INSERT INTO test(timestamp_col) VALUES('2013-03-31 01:59:59');
-- Affected rows: 1  Found rows: 0  Warnings: 0  Duration for 1 query: 0.078 sec.

INSERT INTO test(timestamp_col) VALUES('2013-03-31 02:00:00');
-- SQL Error (1292): Incorrect datetime value: '2013-03-31 02:00:00' for column 'timestamp_col' at row 1

INSERT INTO test(timestamp_col) VALUES('2013-03-31 03:00:00');
-- Affected rows: 1  Found rows: 0  Warnings: 0  Duration for 1 query: 0.063 sec.

这篇关于MySQL不正确的日期时间值错误的有效日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！