I have an application which produces log files. These log files include a
Timestamp field in the format
(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
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
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.