2

I have an application which produces log files. These log files include a Timestamp field in the format 2015-08-25T09:35:01 UTC. (there will be apx 60 logs produced at 0.25 GB per day).

I need to import these log files into MySQL for analysis. But I have a problem converting the Timestamp to DateTime.

example:

CREATE TABLE test1 (
  `TIMESTAMP` varchar(25) DEFAULT NULL,
  `EVENT_TIME` datetime DEFAULT NULL
);

INSERT INTO test1 (`TIMESTAMP`)
VALUES 
('2015-08-25T09:35:01 UTC'),
('2015-08-25T09:36:01 UTC'),
('2015-08-25T09:37:01 UTC'),
('2015-08-25T09:38:01 UTC'),
('2015-08-25T09:39:01 UTC');

So far so good. I can now run a SELECT query to get the datetime

SELECT CAST(`TIMESTAMP` AS datetime) FROM test1;

But, if I try to update the table with the datetime format I get an error

UPDATE test1 SET `EVENT_TIME` = CAST(`TIMESTAMP` AS datetime);

Error Code: 1292. Truncated incorrect datetime value: '2015-08-25T09:35:01 UTC'

Is there a way to do this? as I really need the datetime field in the database, so I don't have to do the CAST every time I run a query.

I was also looking to partition the table by date, as there will be a lot of data produced, and so I only want to keep the minimum amount of data, and then drop the oldest partitions once I am done.

  • 1
    DateTime uses different format "2015-08-25 09:35:01" in general, you may skip - : and even spaces, but I thing adding the T and UTC is the problem. You will probably want to use STR_TO_DATE() stackoverflow.com/a/11226328/1786423 unless you can format the timestamp beforehand, – jkavalik Sep 1 '15 at 10:03
  • cheers, unfortunately I get the same result UPDATE test1 SET EVENT_TIME = STR_TO_DATE(TIMESTAMP, '%Y-%m-%dT%H:%i:%s'); Error Code: 1292. Truncated incorrect datetime value: '2015-08-25T09:35:01 UTC' – IGGt Sep 1 '15 at 10:11
  • 2
    UPDATE test1 SET EVENT_TIME = STR_TO_DATE(TIMESTAMP, '%Y-%m-%dT%H:%i:%s UTC'); - you have to match entire string, including the ' UTC' suffix – jkavalik Sep 1 '15 at 10:27
  • excellent, that appears to have fixed it, and I learned something new. cheers. – IGGt Sep 1 '15 at 10:31
  • The manual states that extra characters are ignored: "Scanning starts at the beginning of str and fails if format is found not to match. Extra characters at the end of str are ignored." – Wivku Jul 15 '16 at 14:07

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.