3

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 approximately 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.

5
  • 2
    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, 2015 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, 2015 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, 2015 at 10:27
  • excellent, that appears to have fixed it, and I learned something new. cheers.
    – IGGt
    Sep 1, 2015 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, 2016 at 14:07

1 Answer 1

0
CREATE TABLE partitioned_test1 (
  `TIMESTAMP` varchar(25) DEFAULT NULL,
  `EVENT_TIME` datetime DEFAULT NULL
)
PARTITION BY RANGE (TO_DAYS(`EVENT_TIME`)) (
  PARTITION p0 VALUES LESS THAN (TO_DAYS('2023-01-01')),
  PARTITION p1 VALUES LESS THAN (TO_DAYS('2023-02-01')),
  PARTITION p2 VALUES LESS THAN (TO_DAYS('2023-03-01')),
  ...
  PARTITION pN VALUES LESS THAN MAXVALUE
);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.