如何设置MySQL的时区? [英] How do I set the time zone of 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.

要使时区名称生效，您必须设置时区信息表以供填充:此答案中填充这些表.

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());

要获取timestamp列作为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?

这篇关于如何设置MySQL的时区?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！