admin 管理员组

文章数量: 887021

mysql的timestamp,unix

今日，多次在不同时区的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到一个时区去处理

本文标签： mysql的timestamp Unix

版权声明：本文标题：mysql的timestamp,unix 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.freenas.com.cn/jishu/1686787271h36653.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。