UNIX 时间戳到 MySQL DATETIME
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1281859/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
UNIX Timestamp to MySQL DATETIME
提问by Henk Denneboom
I have a table with statistics and a field named timewith Unix Timestamps.
我有一个包含统计信息的表和一个以timeUnix 时间戳命名的字段。
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:
表中有大约 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
So the time(INT) should be a DATETIME field.
所以time(INT) 应该是一个 DATETIME 字段。
How can I update the Unix Timestamp to MySQL's DATETIME?
如何将 Unix 时间戳更新为 MySQL 的 DATETIME？
回答by Lepidosteus
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
回答by zvrba
- 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.
- 使用 alter table 在同一个表中创建一个具有 datetime 类型的新列（例如 time2）
- 更新统计设置 time2=from_unixtime(time);
- 使用alter table a) 删除时间列，b) 将time2 重命名为time。
回答by Mohamed Ziada
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`;
回答by Bad Loser
Here's a PDO example of how to use FROM_UNIXTIMEin a prepared statement:
这是如何在准备好的语句中使用FROM_UNIXTIME的 PDO 示例：
$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! ]
