mysql的timestamp,unix_time和时区问题

今日，多次在不同时区的MySQL数据库的timestamp字段上遇到各种问题，搅来搅去，越搅越乱，

故此写下此文记录，理清思路。

• 首先澄清一个概念：

所有的Linux系统文件系统底层存储的都是UTC时间，也就是说都是自1970年0时0分0秒以来的UTC标准时间的秒数。

无论系统配置是什么时区，显示如何不同，底层存储都是一致的。

• 如何得到UTC系统时间？

在shell环境下 >date '+%s' 即可得到

在mysql环境下>select unix_timestamp();即可得到

• timestamp是什么？

timestamp是mysql数据库中的一种字段类型，

不用说太多，

• 内部存储是4个字节
• 精度达到微妙
• 回转换成UTC时间存储
• 读取的时候再根据时区转换回来

mysql> select now()+0;
+-----------------------+
| now()+0               |
+-----------------------+
| 20130722184134.000000 |
+-----------------------+
1 row in set (0.00 sec)


mysql> select now();  
+---------------------+
| now()               |
+---------------------+
| 2013-07-22 18:41:37 |
+---------------------+
1 row in set (0.00 sec)

可以看到，我们看到的timestamp实际是已经根据当前时区转换过格式的字符形式

• 如何在mysql中根据timestamp得到UTC系统时间戳呢呢?

很简单，mysql> select unix_timestamp( 20130722183356.000000);
+----------------------------------------+
| unix_timestamp( 20130722183356.000000) |
+----------------------------------------+
|                             1374489236 |
+----------------------------------------+

             

• 如何在mysql中根据UTC 系统时间戳得到当前时区的timestamp呢?

mysql> select from_unixtime(1374489236);
+---------------------------+
| from_unixtime(1374489236) |
+---------------------------+
| 2013-07-22 18:33:56       |
+---------------------------+
1 row in set (0.00 sec)

•  如何在mysql中根据当前时区的时区得到其他时区的timestamp呢?

mysql> SELECT CONVERT_TZ('2013-07-22 18:41:37','+08:00','+00:00') as UTC;         
+---------------------+
| UTC                 |
+---------------------+
| 2013-07-22 10:41:37 |
+---------------------+
1 row in set (0.00 sec)

• 一个罕见的问题

数据库使用Amazon RDS 是无法修改时区的，统一使用UTC时区

应用程序使用Amazon 服务器 是UTC －7 美西时间

数据库部分时间字段使用了current_timestamp，使用了UTC时区

部分字段使用了unix_time,由应用程序插入，理论上也是UTC时间

部分字段使用了unix_time并根据app服务器的时区转成了时间的string格式，导致出现UTC－7的时间

导致了同一IDC的数据有了两种时间

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

结论：

我们不需要被timestamp和时区弄糊涂，其实很简单，timestamp存储的时间是带时区偏移的。

所有的数据库的时间应该统一操作，要么使用DB的current_timestamp，要么使用应用程序插入。

在有多IDC且不同时区的情况下，

如果需要标准UNIX时间戳：我建议只需要每次取出UTC的时间戳进行处理

如果需要标准的timestamp,我建议全部统一convert到一个时区去处理