3

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.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
Géminis
  • 123
  • 2
  • 9
  • Is it possible for you to access the raw sql sencence executed? There are few operations on mysql date/datetime which generate errors depending the mysql version you have. Take a look at [this](https://stackoverflow.com/q/36374335/5476782) question thread. – Evhz Apr 02 '18 at 11:48

1 Answers1

8

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.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • Im from Spain, Im getting rows from a MySQL which runs inside a docker image (official mysql docker image without any specific configuration) and inserting them in a local MySQL which runs with a Spanish timezone. (GMT +1) – Géminis Apr 02 '18 at 11:58