[Maria-discuss] Odd errors with TIMESTAMP columns
Hello, I hope this is the correct list for this sort of question, apologies if not. I am working on importing data from a MySQL 5.1 database into a new MariaDB database (Server version: 10.4.14-MariaDB-1:10.4.14+maria~focal-log mariadb.org binary distribution), and am having trouble with a number of values in TIMESTAMP columns. For example: MariaDB [x]> create table y ( z timestamp ); MariaDB [x]> insert into y values ('2016-03-13 02:05:32'); ERROR 1292 (22007): Incorrect datetime value: '2016-03-13 02:05:32' for column `x`.`y`.`z` at row 1 MariaDB [x]> insert into y values ('2016-03-13 03:05:32'); Query OK, 1 row affected (0.091 sec) I have tried turning off mysql56_temporal_format but that doesn't seem to change anything. As far as I can tell the values should be valid, in the above example simply changing the hour from 2 to 3 allowed the insert to succeed. But I am at a loss as to why '2016-03-13 02:05:32' is not a valid value. There are a few hundred rows at least affected by this, with many different values. Any ideas as to what could be causing this? Or ways to resolve it? Thanks, -David
Sunday, March 13, 2016, 2:00:00 am was the start of Daylight Saving Time in the US. Perhaps you intend to have the database timezone set to UTC? Jonathan On 2021/02/26 11:13, David Buckley wrote:
Hello,
I hope this is the correct list for this sort of question, apologies if not.
I am working on importing data from a MySQL 5.1 database into a new MariaDB database (Server version: 10.4.14-MariaDB-1:10.4.14+maria~focal-log mariadb.org binary distribution), and am having trouble with a number of values in TIMESTAMP columns.
For example:
MariaDB [x]> create table y ( z timestamp ); MariaDB [x]> insert into y values ('2016-03-13 02:05:32'); ERROR 1292 (22007): Incorrect datetime value: '2016-03-13 02:05:32' for column `x`.`y`.`z` at row 1 MariaDB [x]> insert into y values ('2016-03-13 03:05:32'); Query OK, 1 row affected (0.091 sec)
I have tried turning off mysql56_temporal_format but that doesn't seem to change anything. As far as I can tell the values should be valid, in the above example simply changing the hour from 2 to 3 allowed the insert to succeed. But I am at a loss as to why '2016-03-13 02:05:32' is not a valid value. There are a few hundred rows at least affected by this, with many different values.
Any ideas as to what could be causing this? Or ways to resolve it?
Thanks, -David
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Hi, David! This is, apparently, the time when DST was enabled in the time zone in which you're doing the import. On Feb 26, David Buckley wrote:
Hello,
I hope this is the correct list for this sort of question, apologies if not.
I am working on importing data from a MySQL 5.1 database into a new MariaDB database (Server version: 10.4.14-MariaDB-1:10.4.14+maria~focal-log mariadb.org binary distribution), and am having trouble with a number of values in TIMESTAMP columns.
For example:
MariaDB [x]> create table y ( z timestamp ); MariaDB [x]> insert into y values ('2016-03-13 02:05:32'); ERROR 1292 (22007): Incorrect datetime value: '2016-03-13 02:05:32' for column `x`.`y`.`z` at row 1 MariaDB [x]> insert into y values ('2016-03-13 03:05:32'); Query OK, 1 row affected (0.091 sec)
I have tried turning off mysql56_temporal_format but that doesn't seem to change anything. As far as I can tell the values should be valid, in the above example simply changing the hour from 2 to 3 allowed the insert to succeed. But I am at a loss as to why '2016-03-13 02:05:32' is not a valid value. There are a few hundred rows at least affected by this, with many different values.
Any ideas as to what could be causing this? Or ways to resolve it?
Thanks, -David
Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org
participants (3)
-
David Buckley -
Jonathan Nicol -
Sergei Golubchik