mysql utc时间戳_MySQL将UTC时间设置为默认时间戳

要同意@ ypercube的注释,即CURRENT_TIMESTAMP存储为UTC但作为当前时区检索,您可以使用

–default_time_zone选项影响服务器的时区设置以进行检索.这使您的检索始终为UTC.

默认情况下,选项为“SYSTEM”,这是您的系统时区设置方式(可能是也可能不是UTC！)：

mysql> SELECT @@global.time_zone, @@session.time_zone;

+--------------------+---------------------+

| @@global.time_zone | @@session.time_zone |

+--------------------+---------------------+

| SYSTEM | SYSTEM |

+--------------------+---------------------+

1 row in set (0.00 sec)

mysql> SELECT CURRENT_TIMESTAMP();

+---------------------+

| CURRENT_TIMESTAMP() |

+---------------------+

| 2012-09-25 16:28:45 |

+---------------------+

1 row in set (0.00 sec)

您可以动态设置：

mysql> SET @@session.time_zone='+00:00';

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@global.time_zone, @@session.time_zone;

+--------------------+---------------------+

| @@global.time_zone | @@session.time_zone |

+--------------------+---------------------+

| SYSTEM | +00:00 |

+--------------------+---------------------+

1 row in set (0.00 sec)

或永久在你的my.cnf：

[mysqld]

**other variables**

default_time_zone='+00:00'

重新启动服务器,您将看到更改：

mysql> SELECT @@global.time_zone, @@session.time_zone;

+--------------------+---------------------+

| @@global.time_zone | @@session.time_zone |

+--------------------+---------------------+

| +00:00 | +00:00 |

+--------------------+---------------------+

1 row in set (0.00 sec)

mysql> SELECT CURRENT_TIMESTAMP();

+---------------------+

| CURRENT_TIMESTAMP() |

+---------------------+

| 2012-09-25 20:27:50 |

+---------------------+

1 row in set (0.01 sec)