Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL 1292 Incorrect datetime value

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.

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.

like image 550
CycleGeek Avatar asked Feb 24 '16 13:02

CycleGeek


People also ask

How do I insert date in YYYY-MM-DD format in MySQL?

Introduction to MySQL DATE data type This format is fixed and it is not possible to change it. For example, you may prefer to use mm-dd-yyyy format but you can't. Instead, you follow the standard date format and use the DATE_FORMAT function to format the date the way you want. MySQL uses 3 bytes to store a DATE value.

What is the datetime format in MySQL?

MySQL retrieves and displays DATETIME values in ' YYYY-MM-DD hh:mm:ss ' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59' . The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

Can we change date format in MySQL?

Change the curdate() (current date) format in MySQL The current date format is 'YYYY-mm-dd'. To change current date format, you can use date_format().


1 Answers

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.

Change the system timezone to the one that does not use DST and you should be fine.

like image 146
nik Avatar answered Oct 04 '22 02:10

nik