如何设置 MySQL 的时区?
在一台服务器上,当我运行时:
On one server, when I run:
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2009-05-30 16:54:29 |
+---------------------+
1 row in set (0.00 sec)
在另一台服务器上:
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2009-05-30 20:01:43 |
+---------------------+
1 row in set (0.00 sec)
推荐答案
我认为这可能有用:
default-time-zone='+00:00'
@@global.time_zone 变量
查看它们设置的值:
@@global.time_zone variable
To see what value they are set to:
SELECT @@global.time_zone;
要为其设置一个值,请使用其中之一:
To set a value for it use either one:
SET GLOBAL time_zone = '+8:00';
SET GLOBAL time_zone = 'Europe/Helsinki';
SET @@global.time_zone = '+00:00';
(使用诸如欧洲/赫尔辛基"之类的命名时区意味着您必须正确填充时区表.)
(Using named timezones like 'Europe/Helsinki' means that you have to have a timezone table properly populated.)
请记住,+02:00
是一个偏移量.Europe/Berlin
是一个时区(有两个偏移量),CEST
是对应于特定偏移量的时钟时间.
Keep in mind that +02:00
is an offset. Europe/Berlin
is a timezone (that has two offsets) and CEST
is a clock time that corresponds to a specific offset.
SELECT @@session.time_zone;
要设置它,请使用其中之一:
To set it use either one:
SET time_zone = 'Europe/Helsinki';
SET time_zone = "+00:00";
SET @@session.time_zone = "+00:00";
两者都可能返回 SYSTEM,这意味着它们使用 my.cnf 中设置的时区.
Both might return SYSTEM which means that they use the timezone set in my.cnf.
要使时区名称起作用,您必须设置需要填充的时区信息表:http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.html.我还在这个答案中提到了如何填充这些表格.
For timezone names to work, you must setup your timezone information tables need to be populated: http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.html. I also mention how to populate those tables in this answer.
SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP);
如果您的时区为 +2:00,它将返回 02:00:00.
It will return 02:00:00 if your timezone is +2:00.
SELECT UNIX_TIMESTAMP();
SELECT UNIX_TIMESTAMP(NOW());
获取时间戳列作为 UNIX 时间戳
SELECT UNIX_TIMESTAMP(`timestamp`) FROM `table_name`
获取 UTC 日期时间列作为 UNIX 时间戳
SELECT UNIX_TIMESTAMP(CONVERT_TZ(`utc_datetime`, '+00:00', @@session.time_zone)) FROM `table_name`
注意:更改时区不会更改存储的日期时间或时间戳,但它会为现有时间戳列显示不同的日期时间,因为它们在内部存储为 UTC 时间戳并在当前 MySQL 中外部显示时区.
Note: Changing the timezone will not change the stored datetime or timestamp, but it will show a different datetime for existing timestamp columns as they are internally stored as UTC timestamps and externally displayed in the current MySQL timezone.
我在这里做了一个备忘单:MySQL应该有它的时区设置为 UTC?
I made a cheatsheet here: Should MySQL have its timezone set to UTC?
相关文章