关于数据库时间字段的时区问题

大多数数据库都提供了两种列类型：自带时区和不带时区的时间戳，比如：
MySQL：DATETIME （自带时区）和 TIMESTAMP （不带时区）
PostgresQL：TIMESTAMP WITH TIME ZONE （自带时区）和 TIMESTAMP （不带时区）
所谓自带时区，就是数据库不会帮你做额外的转换，你写入什么时间，读出来就是什么时间
所谓不带时区，就是数据库实际上会保存 UTC 时间戳，写入的时候先按 Session 时区转成 UTC 时间，读出的时候再按 Session 时区转成当前时区的时间这些转换都是透明的
总结一下，你的这个需求，应该用不带时区的时间戳作为列类型，然后一切就搞定了

@fuyufjh 使用 utc 的时候，统计的逻辑会复杂很多呀

@chenqh “这些转换都是透明的”

@fuyufjh 没有搞懂， 假如我想统计这个月每天的订单数目，这个天肯定是我本地时间的天呀

@chenqh UTC 转本地时间还嫌麻烦吗老哥…

@fuyufjh 说反了吧？

@chenqh 如果你觉得以后会支持前端跑在不同时区，比如从国外访问，那你数据库就存 UTC。
查询的时候让前台给你传 UTC 时间。或者只存个 offset。
一般这些 ORM 都可以给你做了，配置一下就好了。
如果你这个产品从头到尾都不会有人在不同时区使用，那就直接本地时间也没问题。

@noqwerty 只是一点点麻烦而已，关键是统计的时候

存成 unix 时间戳

@suotm 那统计表的按天统计怎么办？ 使用时间戳或者 utc,居然会导致数据库里面存在两种时区？

突然发现一个问题，v2ex 的东 8 区时间，好像差了几分钟

@chenqh 看错了

@fuyufjh 这里的数据库隐式转换，包括驱动吗？

客户端连接数据库之后，设置本次连接的时区，这样 timestamp 字段读写就能自动转换了
SET time_zOne= timezone;

@chenqh 如果是 PG，设置客户端的连接时区， 所有 timestamp with time zone 都会自动按照设置的时区处理时间。

我们有澳洲，香港，英国的项目，都是用的 datetime 类型

@chenqh 不太明白你纠结的点在哪里，如果是不知道怎么聚合查询的话，可以这样写 where DATE(created_at) = 'xxxx-xx-xx'

我直接存 int 只由代码处理时区。

我也比较好奇这个问题，已经好久没有用时间类型了，一直是 int 存储。

@keepeye 他纠结的问题就在这里，如果 created_at 不带时区的话，不能按照 local date 的方式统计。

@optional 还是没明白，连接的时候指定下本地时区，查询的时候数据库会自动将 created_at 转换成本地时区

不信就做个试验吧，可能很多人还不理解 connection 可以设置时区

mysql> select created_at from users limit 1;
+---------------------+
| created_at |
+---------------------+
| 2019-03-01 02:14:53 |
+---------------------+
1 row in set (0.00 sec)

mysql> set time_zOne='-8:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select created_at from users limit 1;
+---------------------+
| created_at |
+---------------------+
| 2019-02-28 10:14:53 |
+---------------------+
1 row in set (0.00 sec)

mysql> select date(created_at) from users limit 1;
+------------------+
| date(created_at) |
+------------------+
| 2019-02-28 |
+------------------+
1 row in set (0.00 sec)

@keepeye 我也是这个意思。

@keepeye 之前都不知知道 connection 可以设置时区，但是我想了一下，有一个问题，使用了带时区的 connection,insert 或者 update 的时候， 时间必须是本地时间？

@chenqh 对，mysql 会根据 connection 时区，将你本地写的值转换为 utc 存储，你只要设置好你本地和连接的时区就行了，数据转换对你来说是透明的

@keepeye 这个样子好复杂呀，假如一步出错，估计要查好久，算了，以后我还是用 timestamp 吧

@optional 麻烦问下 postgre 怎么在连接上设置时区我查到的都是修改 jvm 的参数