开发人员执行如下SQL
root@localhost{wm_ztcj} >select timestampdiff(second,'1970-1-1','2014-07-23 9:18:40') as timestamp;
+------------+
| timestamp |
+------------+
| 1406107120 |
+------------+
1 row in set (0.00 sec)
在代码中传递获取到的时间戳并用from_unixtime函数还原日期
mysql> select from_unixtime(1406107120);
+---------------------------+
| from_unixtime(1406107120) |
+---------------------------+
| 2014-07-23 17:18:40 |
+---------------------------+
1 row in set (0.00 sec)
返回的时间值相差8个小时,查看当前系统的时间戳
root@localhost{wm_ztcj} >\! date
Wed Jul 23 10:49:09 CST 2014
查看数据库的时区设定
mysql> show variables like '%system_time%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| system_time_zone | CST |
+------------------+-------+
1 row in set (0.00 sec)
这个值默认是与操作系统的时区相同
查阅官档:
http://dev.mysql.com/doc/refman/5.6/en/time-zone-support.html
在mysqld启动之后默认由system_time_zone参数控制,而每个会话的时区设定则由time_zone变量控制
mysql> select @@global.system_time_zone,@@time_zone;
+---------------------------+-------------+
| @@global.system_time_zone | @@time_zone |
+---------------------------+-------------+
| CST | SYSTEM |
+---------------------------+-------------+
1 row in set (0.00 sec)
由于此时的系统时区是CST=GMT+8,所以把time_zone替换成+00:00就是GMT了
mysql> set time_zone='+00:00';
Query OK, 0 rows affected (0.00 sec)
mysql> select from_unixtime(1406107120);
+---------------------------+
| from_unixtime(1406107120) |
+---------------------------+
| 2014-07-23 09:18:40 |
+---------------------------+
1 row in set (0.00 sec)
至于from_unixtime与unix_timestamp的差别在另一篇博客中记录。