UNIX Timestamp to MySQL DATETIME

Question

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?

You can see how to convert timestamp in datetime in http://www.jordigirones.com/455-convertir-unix-timestamp-en-fecha-de-mysql.html — user1506261
– user1506261, Commented Jul 6, 2012 at 9:08

Lepidosteus · Accepted Answer · 2009-08-15 13:33:11Z

31

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

edited Aug 15, 2009 at 13:33

answered Aug 15, 2009 at 13:22

Lepidosteus

12.1k4 gold badges41 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Henk Denneboom Over a year ago

Works great, last one should be ALTER TABLE stats. Thanks!

Abadis Over a year ago

Great Job! Worked Perfectly!

zvrba · Accepted Answer · 2009-08-15 13:20:52Z

3

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.

answered Aug 15, 2009 at 13:20

zvrba

24.7k3 gold badges57 silver badges66 bronze badges

Comments

sth · Accepted Answer · 2010-04-27 02:43:44Z

0

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`;

edited Apr 27, 2010 at 2:43

sth

231k56 gold badges288 silver badges370 bronze badges

answered Apr 26, 2010 at 14:33

Mohamed Ziada

311 silver badge3 bronze badges

Comments

Bad Loser · Accepted Answer · 2019-07-27 03:36:12Z

0

Here's a PDO example of how to use FROM_UNIXTIME in a prepared statement:

$query = "INSERT INTO " .$this->table_name.  " SET origTS=FROM_UNIXTIME(:d2)";                
$stmt = $this->conn->prepare($query);
$stmt->bindParam(":d2", $this->origTS);         // UNIX time [ but seconds! ]

answered Jul 27, 2019 at 3:36

Bad Loser

3,1551 gold badge21 silver badges32 bronze badges

Collectives™ on Stack Overflow

UNIX Timestamp to MySQL DATETIME

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related