UNIX时间戳转换为MySQL日期时间格式

Question

我有一个带有统计信息的表格，其中有一个名为time的字段，它包含Unix Timestamps。

这个表格有大约200行，但是我想把Unix时间戳改为MySQL DATETIME格式，并保留当前所有行。当前的表格如下：

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

因此，time（INT）应该是一个DATETIME字段。

我如何将Unix时间戳更新为MySQL的DATETIME？

- Henk Denneboom

---

您可以在以下链接中看到如何将时间戳转换为日期时间格式：http://www.jordigirones.com/455-convertir-unix-timestamp-en-fecha-de-mysql.html。 - user1506261

Answer 1

在实际使用之前，记得对其进行测试，这是我凭记忆写下的，但应该能给你一个好的想法。

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

- Lepidosteus

---

非常好，最后一个应该是 ALTER TABLE stats。谢谢！ - Henk Denneboom

干得好！完美地工作了！ - Abadis

Answer 2

1. 使用 alter table 命令在同一张表中创建一个新列（例如 time2），其类型为 datetime。
2. 执行以下代码：update stats set time2=from_unixtime(time);
3. 使用 alter table 命令：a) 删除 time 列，b) 将 time2 重命名为 time。

- zvrba

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

- Mohamed Ziada

Answer 4

这是一个使用 PDO 的例子，展示如何在准备好的语句中使用 FROM_UNIXTIME：

$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! ] 

- Bad Loser