UNIX Timestamp to MySQL DATETIME
27,899
Solution 1
Remember to test it before using it for real, this is written from memory but should give you a good idea.
ALTER TABLE `stats` CHANGE `time` `unix_time` int(11) NOT NULL // rename the old column
ALTER TABLE `stats` ADD `time` DATETIME NOT NULL // create the datetime column
UPDATE `stats` SET `time`=FROM_UNIXTIME(unix_time) // convert the data
ALTER TABLE `stats` DROP `unix_time` // drop the old unix time column
Solution 2
- use alter table to create a new column (eg. time2) with the datetime type in the same table
- update stats set time2=from_unixtime(time);
- use alter table to a) delete the time column, and b) rename the time2 to time.
Author by
Henk Denneboom
Updated on July 27, 2020Comments
-
Henk Denneboom almost 4 years
I have a table with statistics and a field named
time
with Unix Timestamps.There are about 200 rows in the table, but I would like to change the Unix timestamps to MySQL DATETIME without losing the current rows. The current table:
CREATE TABLE `stats` ( `id` int(11) unsigned NOT NULL auto_increment, `time` int(11) NOT NULL, `domain` varchar(40) NOT NULL, `ip` varchar(20) NOT NULL, `user_agent` varchar(255) NOT NULL, `domain_id` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8
So the
time
(INT) should be a DATETIME field.How can I update the Unix Timestamp to MySQL's DATETIME?