MySQL 和时区

Question

假设下表:

CREATE TABLE IF NOT EXISTS `test` (
  `stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

当我运行以下 SQL 时:

SET time_zone = '+00:00';
INSERT INTO `test` (`stamp`) VALUES (CURRENT_TIMESTAMP);

test 中的值是我机器的本地时间而不是 UTC。

根据 MySQL site

Values for TIMESTAMP columns are converted from the current time zone to UTC for storage, and from UTC to the current time zone for retrieval.

我做错了什么？我想用 UTC 日期/时间戳存储我的记录，并在用户的本地时区检索它们。

Answer 1

根据 MySQL 5.5 文档，使用 TIMESTAMP 数据类型将默认使用服务器的时区，除非您从客户端更改变量。

发件人:http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html

Per-connection time zones. Each client that connects has its own time zone setting, given by the session time_zone variable. Initially, the session variable takes its value from the global time_zone variable, but the client can change its own time zone with this statement:

mysql> SET time_zone = timezone;