UNIX Timestamp to MySQL DATETIME

Question

Hi all,

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.

What would be the best way to update the Unix Timestamp to MySQL's DATETIME?

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.

Thanks in advance!

Answer 1

1. use alter table to create a new column (eg. time2) with the datetime type in the same table
2. update stats set time2=from_unixtime(time);
3. use alter table to a) delete the time column, and b) rename the time2 to time.

Answer 2

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

Answer 3

ALTER TABLE `stats`
MODIFY COLUMN `time` timestamp NULL DEFAULT '0000-00-00 00:00:00' AFTER `id`;
ALTER TABLE `stats`
MODIFY COLUMN `time` datetime NULL DEFAULT '0000-00-00 00:00:00' AFTER `id`;