1

I am trying to copy logs from one of my MySQL 5.6 databases to another. Unfortunately, it keeps failing when it tries to insert one of the times with the following error message:

ERROR 1292 (22007): Incorrect datetime value: '2015-03-29 01:01:12' for column 'when' at row 1

To simulate the issue, create a logs table like so:

CREATE TABLE `logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `message` text NOT NULL,
  `context` longtext NOT NULL,
  `priority` int(1) NOT NULL,
  `when` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `priority` (`priority`),
  KEY `when` (`when`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 

Then try to insert a log like so:

INSERT INTO logs (message, context, priority, `when`) VALUES('hello', 'hello', 1, "2015-03-29 01:01:12");

If I change the date in any way, such as make it 2016, or set it to the 30th of March, it will work, but changing the time only sometimes causes it to work. Also, stripping off the time (e.g. "2015-03-29") also appears to work. Is there something wrong with my query?

Version Info

  • mysql Ver 14.14 Distrib 5.6.39, for Linux (x86_64) using EditLine wrapper
  • OS: Ubuntu 16.04
  • What's the system's timezone and what is the timezone of the system where the data come from? – ypercubeᵀᴹ Mar 20 '18 at 9:50
  • 1
    To help you understand the issue, there are some timezones that apply DST (daylight saving time) and the change happened at 2015-03-29 (clocks moved forward at 01:00:00 to 02:00:00). In those timezones, the timestamp 2015-03-29 01:01:12 is not valid. Clocks never showed that ime ;) – ypercubeᵀᴹ Mar 20 '18 at 9:54
  • That would make sense (we are UK based), however, I understood that "timestamp" type to always be UTC and if you wanted timezones to be a factor, you had to use the datetime type? e.g from the docs: "TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC. " – Programster Mar 20 '18 at 12:16
  • Ok looks like I had not taken into account: "MySQL converts TIMESTAMP values from the current time zone to UTC for storage" so I just need to ensure my servers are all set to UTC not BST. – Programster Mar 20 '18 at 12:18
  • @ypercube can you just put in an answer stating that due to DST, that time was not valid as MySQL converts to/from local timezone, and a workaround is to make sure all systems are set to UTC? Then I'll mark that as the answer. – Programster Mar 20 '18 at 12:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.