MySQL incorrect datetime value error for valid dates

Question

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

For example 2013-03-31 02:13:11 and 2014-03-31 02:55:00 work, but 2013-03-31 02:55:00 says:

SQL Error (1292): Incorrect datetime value

What could be the problem?

So I was Googling and found out that daylight saving time 2013 in Romania began at 3:00 am on Sunday, 31 March. Could that be the issue? — Salman Arshad
– Salman Arshad, Commented Apr 11, 2019 at 14:57
I don't have any special chars. I just edit it in HeidiSQL manually. — Alex
– Alex, Commented Apr 11, 2019 at 14:58

Salman Arshad · Accepted Answer · 2019-04-11 19:52:07Z

1

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!

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.

edited Apr 11, 2019 at 19:52

answered Apr 11, 2019 at 18:40

Salman Arshad

274k85 gold badges450 silver badges540 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alex Over a year ago

This kinda makes sense. But it's weird that 2:13 works, while 2:55 doesn't.

Collectives™ on Stack Overflow

MySQL incorrect datetime value error for valid dates

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related