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到一个时区去处理

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值