postgres10.2时区研究

搭建两个虚拟环境，操作系统均是cents7。

环境A：

使用timedatectl命令查看时区为 Time zone: Asia/Shanghai (CST, +0800)。

本地数据库时区(show timezone命令)为PRC，等价于cst。

环境B：

时区为America/New_York (EST, -0500)，本地数据库时区为US/Eastern，等价于EST。

1. 先针对timestamp with time zone和timestamp without time zone两个配置进行测试。

在环境A的数据库建立数据表并写入数据：

CREAT TABLE test_timestamp(
        ttz     timestamp with time zone,
        twtz  timestamp without time zone
);
INSERT INTO test_timestamp VALUES (now(),now());

查看数据：

postgres=# table test_timestamp;
              ttz              |            twtz            
-------------------------------+----------------------------
 2019-02-15 11:28:26.994804+08 | 2019-02-15 11:28:26.994804
(1 row)

 在环境B中，使用python查看：

>>> data = pd.read_sql(''' select * from test_timestamp  ''', conn)
>>> data
                               ttz                       twtz
0 2019-02-15 03:28:26.994804+00:00 2019-02-15 11:28:26.994804
>>> 

会发现ttz字段自动转换成了UTC时间，而twtz字段原封不动的输出。

再测试环境B写入数据：

本地时间为：$ date
Thu Feb 14 22:33:42 EST 2019
执行cur.execute(''' insert into test_timestamp values('2019-02-14 22:33:42','2019-02-14 22:33:42')  ''')

在A的数据库中查看：

postgres=# table test_timestamp;
              ttz              |            twtz            
-------------------------------+----------------------------
 2019-02-15 11:28:26.994804+08 | 2019-02-15 11:28:26.994804
 2019-02-14 22:33:42+08        | 2019-02-14 22:33:42
(2 rows)

发现ttz字段日期值加上了环境A的时区，出现了偏差。因为插入语句用的是字符串类型，是的数据库默认为本地时区。

写入数据时如果调用sql函数：

cur.execute(''' insert into test_timestamp values(now(),now())  ''')

在环境A中：

postgres=# table test_timestamp;
              ttz              |            twtz            
-------------------------------+----------------------------
 2019-02-15 11:28:26.994804+08 | 2019-02-15 11:28:26.994804
 2019-02-14 22:33:42+08        | 2019-02-14 22:33:42
 2019-02-15 11:41:13.080922+08 | 2019-02-15 11:41:13.080922
(3 rows)

插入的也是环境A的本地时间。也就是说，最后都是以数据库的环境来执行。

 

结论：

A. 同一时区内，没有差别。跨时区时，如果只是跨时区读（写操作由一个特定时区完成），可以看成是无差别，都需要转换一下，设置成with time zone可能会好一些，因为在读取的时候有些工具或语言（比如java）会自动转成当地时区的时间。如果是跨时区写，那么就要设置成without time zone，要不然数据库记录的时间会有错误，因为写的时候传的是字符串，数据库会加上本地时区。

B. 不同数据库时间类型的名称不一样，postgres里面没有datetime类型，用timestamp表示datetime；在mysql里有datetime类型，也有timestamp类型（含义和postgres里面不一样）。表示的范围大小、是不是带有时区信息也要查看具体数据库的手册。

C. postgres日期类型一般都是“YYYY-MM-DD HH:mm:ss”格式，不接受一般理解上的数字类型的时间戳（int型数值）的输入。时间类型（比如updatetime和tradedate等）的设置不一定得是数值格式，也可以是日期格式，只是需要注意好时区问题。