MySQL 1292 Incorrect datetime value

mysql sql-timestamp mysql-error-1292
CycleGeek picture CycleGeek · Feb 24, 2016 · Viewed 61.3k times · Source

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.

Answer

nik picture nik · Mar 12, 2016

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.

Related questions

SqlAlchemy TIMESTAMP 'on update' extra

I am using SqlAlchemy on python3.4.3 to manage a MySQL database. I was creating a table with: from datetime import datetime from sqlalchemy import Column, text, create_engine from sqlalchemy.types import TIMESTAMP from sqlalchemy.dialects.mysql import BIGINT from …

Read More
How do I import an SQL file using the command line in MySQL?

I have a .sql file with an export from phpMyAdmin. I want to import it into a different server using the command line. I have a Windows Server 2008 R2 installation. I placed the .sql file on the C drive, and …

Read More
How can I prevent SQL injection in PHP?

If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example: $unsafe_variable = $_POST['user_input']; mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')"); That's …

Read More