MySQL datetime与TIMESTAMP的时区

OK I have a MySQL DB in UTC timezone, I don't want to change that (it is a AWS RDS). I am running all queries through php_mysqli I am using the following after connection to set timezone:

好的我在UTC时区有一个MySQL数据库,我不想改变它(它是一个AWS RDS)。我通过php_mysqli运行所有查询我在连接到set time后使用以下内容:

SET time_zone = "-05:00"

I have a table test with 2 fields: date_add => DATETIME date_upd => TIMESTAMP

我有一个包含2个字段的表测试:date_add => DATETIME date_upd => TIMESTAMP

The MySQL NOW() and CURRENT_TIMESTAMP match and are in right timezone. Confirmed my running:

MySQL NOW()和CURRENT_TIMESTAMP匹配并处于正确的时区。确认我的跑步:

SELECT NOW()
SELECT CURRENT_TIMESTAMP

When I run the following:

当我运行以下内容时:

INSERT INTO `test` SET `date_add`=NOW()

then

SELECT * FROM `test`

I get matching date_add and date_upd with correct time zone

我得到正确的时区匹配date_add和date_upd

However when I look at the raw table in CLI or phpMyAdmin the results are different, like in different timezones?

但是,当我在CLI或phpMyAdmin中查看原始表时,结果会有所不同,就像在不同的时区一样?

+---------------------+---------------------+
| date_add            | date_upd            |
+---------------------+---------------------+
| 2013-06-07 15:57:09 | 2013-06-07 20:57:09 |
+---------------------+---------------------+

Not sure if the 2 fields are just set to differnt time zones are what is going on any help with this would be greatly appreciated... thx

不确定2个字段是否只是设置为不同的时区是什么正在发生任何帮助,这将是非常感谢... thx

3 个解决方案

#1

my guess would be that set timezone just sets the local environment variable and has no affect on what is actually saved. it just affects how it is viewed when queried.

我的猜测是set timezone只设置本地环境变量,对实际保存的内容没有影响。它只会影响查询时的查看方式。

in phpmyadmin you dont do the same set first, so you see it with default timezone.

在phpmyadmin你首先不要做相同的设置,所以你用默认时区看到它。

#2

That is because datetime does not have a timezone, whereas timestamp have timezone. http://dev.mysql.com/doc/refman/5.0/en/datetime.html

这是因为datetime没有时区,而timestamp有时区。 http://dev.mysql.com/doc/refman/5.0/en/datetime.html

#3

In MySQL, timestamp in fact is always in UTC, i.e., when you insert a timestamp at your local time zone to db, it's automatically converted to UTC on the database server. So it's consistent everywhere. I'd recommend that you always use timestamp if there are clients in different time zones. Also timestamp is more efficient than datetime.

在MySQL中,时间戳实际上始终是UTC,即,当您在本地时区向db插入时间戳时,它会在数据库服务器上自动转换为UTC。所以到处都是一致的。如果有不同时区的客户端,我建议您始终使用时间戳。时间戳也比datetime更有效。

#1