CODEWITHSUNDEEP
mysqldatetimetimestamp
Alex
Alex

Reputation: 4774

MySQL incorrect datetime value error for valid dates

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?

Upvotes: 0

Views: 2313

Answers (1)

Salman Arshad
Salman Arshad

Reputation: 272066

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.

Upvotes: 1

Related Questions