Search code examples
pythonmysqldatetimetimezonedst

Python MySQLdb Incorrect datetime value: '2018-03-25 02:00:02'

I'm getting this error on Python using MySQLdb of mysqlclient package.

_mysql_exceptions.OperationalError: 
(1292, "Incorrect datetime value: '2018-03-25 02:00:02' for column 'start' at row 1")

The code that causes the error:

conn.cursor.execute(query.format(table=table), 
(row['id'], row['type'], row['start'], row['end'],       
row['raw_xml'], row['id_parent'], row['rango']))

I can insert the rest of rows correctly but when I try to insert this datetime object it crashes.

The field in the database its a timestamp field and It works with the rest of datetime objects but dont know why when it tries to make this statement crashes.

Thanks in advance.

Solution

  • Central European Time switched from standard time to daylight time at 2018-03-25 02:00 local time.

    This is most likely a timezone conversion error on the day of standard-time to daylight-time switchover. The hour from 0200 to just before 0300 does not exist. Your time value 2018-03-25 02:00:02 doesn't exist in Central European time.

    This gets you 2018-03-25 03:00:00.

    SET time_zone = 'Europe/Madrid';
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP('2018-03-25 02:30'));

    With this cheezy table definition:

    CREATE TABLE timetest (
    id INT NOT NULL AUTO_INCREMENT,
    ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `PK` (`id`)
);

    running this INSERT gets you the Incorrect DATETIME value error.

    INSERT INTO timetest (ts) VALUES ('2018-03-25 02:30');

    but running this hack works correctly and puts 03:00 into the table.

    INSERT INTO timetest (ts) VALUES (FROM_UNIXTIME(UNIX_TIMESTAMP('2018-03-25 02:30')));

    Your best bet is to figure out how you came up with that invalid date/time value, and correct your mistake. Your second best bet is this hack.

    You could also change your TIMESTAMP column to a DATETIME column, and store all your timestamps in local time.