mysql CURRENT_TIMESTAMP 给我全部 0

Question

如果我运行以下查询:

ALTER TABLE `price_s` ADD COLUMN `ts` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP  AFTER `price_s_id` ;

新列将填充 0000-00-00 00:00:00。在我修改该列之前，该列中的值不会更新为当前时间戳，即 ON UPDATE CURRENT_TIMESTAMP 似乎工作正常。

如果我运行:

SELECT version( ) , @@time_zone , @@system_time_zone , NOW( ) , UTC_TIMESTAMP( )

我得到了:

'5.5.15', '+10:00', 'EST', '2012-08-23 14:56:59', '2012-08-23 04:56:59'

我想在 ts 列中存储 UTC 时间，我该怎么做？

不确定这是否相关，我已尝试生成 mysql.time_zone* 表:

Miranda-Macbook: ./mysql_tzinfo_to_sql /usr/share/zoneinfo | ./mysql -p -u root mysql
Enter password: Warning: Unable to load '/usr/share/zoneinfo/+VERSION' as time zone. Skipping it.

Warning: Unable to load '/usr/share/zoneinfo/Asia/Riyadh87' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/Asia/Riyadh88' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/Asia/Riyadh89' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/Mideast/Riyadh87' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/Mideast/Riyadh88' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/Mideast/Riyadh89' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.

感谢@Sameer 和@Marc B。现在我更了解时间戳了。(借助这个 article )它已经存储在 UTC 中，所以我需要的实际上是:

SET time_zone = 'SYSTEM';
UPDATE tablename SET ts=now()

Answer 1

INSERT INTO yourtable (ts) VALUES (now())
UPDATE yourtable SET ts=now()

只要您向 ts 字段中填充的内容是有效的 mysql 时间戳字符串 (yyyy-mm-dd hh:mm:ss)，时区是什么并不重要。这仅在转换或检索时变得相关 - mysql 日期字段本身没有时区概念，它们只是一些日期/时间数据的存储库。