POSTGRESQL 带时区的日期的技术与狠活

最近最热门的歇后语就是，“技术与狠活”， 数据库中的POSTGRESQL 的日期数据有什么技术与狠活，咱们今天来说说。

从技术的角度来说， 基于POSTGRESQL 数据库中的数据格式，相对于其他的数据库，类型是丰富的这里主要是POSTGRESQL 的时间是可以带有时区的，也就是with zone 。

首先POSTGRESQL 中的带有时区的日期格式包含了，时间和日期两种，这里官方建议大家使用日期类型的而不是直接使用时间类型的带有时区的类型。

在使用时区的格式时，实际上POSTGRESQL 是只存储一种时间就是UTC的时间格式，通过UTC 的时间对应当前系统的时区来进行日期和时间的显示。

select * from pg_timezone_names; 表中可以找到不同时区对应UTC 后的需要加减的时间，实际上显示的时间可以随着数据在不同时区的流转进行变化的原理也是来自于提到的，存储的时间本身是UTC的时间，在根据数据所处的时区进行相应的时间的加减而得出的。

在开始相关演示开始之前我们有一些关于时区的知识需要拉平

1 POSTGRESQL 的时区的系统是依赖于谁，可能是系统，也可能是POSTGRESQL 本身提供的时区数据。

这点我们通过 pg_config 来判断相关的postgresql到底使用的是哪个时区的数据对数据库进行的支持。这里我们通过命令 pgconfig --configure 来查看当前的我们的POSTGRESQL 编译时的是否加载了 --with-system-tzdata 这个编译想，如果选择了这个项目则你使用的是操作系统提供的时区信息的支持，而不是数据库系统本身提供的时区的支持。

2 修改时区后，在当前的线程中，不会生效，需要在修改后，在开新的进程来访问数据库，才能实现新的时区

只有新开的客户进程的时区才能改变，这点需要注意。

3 时区的显示问题

关于时区的问题，在POSTGRESQL UTC 本身并不存在，换来的是POSTGRESQL 中的epoch ，这主要是于计算机发明的时间有关，计算机中的时间并不是我们日常看到的时间表达方式，在POSTGRESQL 中时间的表达 epoch, 我们下面看看当前的时间如何用epoch 来表达。

在具体时间的显示中，如果是带有时区的时间，是带有 + - 号和数字在后面表达具体的时区信息了，如上面表达是 东八时区

4 什么时候不能使用时区 with time zone

在进行分区表的过程中，time with zone 的时间类型是不能被使用的，这个问题也比较好理解，主要的问题是如果时间变化的情况下，分区的数据的分配和存储会成为一个无法解决的问题。

5 分清出带有时区和不带有时区日期的显示的状态

SELECT now(),

now()::timestamp,

now() AT TIME ZONE 'CST',

now()::timestamp AT TIME ZONE 'CST'；

从上面的四个命令中，其中now() 本身是带有时区性质的，这点需要被确认，另外在日期后面也可以改变时间的本身的时区。另外需要注意的是变换日期的问题

now() at time zone 'cst'

now()::timestamp at time zone 'cst'

可以看到前面的是不带有时区的，后面是带有时区的，而在上面两个一个是带有日期转换的，所以通过上面的测试得出以下结论

1 now 函数本身是一个带有时区的时间函数

2 经过timestamp 转换的为非带有时区意义的时间

3 无时区意义的时间在经过指定时区后，会变为带有时区意义的时间

那么问题来了，如果在转换字符为时间类型后，还能带有时区

实际上我们可以通过timestamptz 类型来表达带有时区的日期转换

如果需要变化当前的时间作为另一个时区的时间可以通过下面的方式来操作

select timestamptz '2022-09-28 17:00:00 Europe/Rome';

我们做一个相关的时间的例子, 看看time with zone 的字段是否可以随着时区的变化，而改变其中的时间。

create table time_test(id INT PRIMARY KEY NOT NULL, time_zone timestamp with time zone);

insert into time_test (id,time_zone) values (1,'2022-09-28 15:00:00');

show timezone;

SET TIME ZONE 'Europe/Rome';

select * from time_test;

从上图可以看到，我们的time with zone 的字段的时间的确可以通过在session级别中变化时区，来改变select 出表的时间值。

那么到底这个time with zone 的字段类型应该怎么用，在什么业务的情况下使用，我们举例：

有一家跨国企业，其中存在产品的订单，而主分析数据库在上海，但上海的物理数据库中，要存储来自世界各地的数据库，并且在不同的数据库中，显示当地的时间的订单信息，那么我们怎么完成这个工作。

这里我们选择这家公司的三个分公司，分别是 柏林， 纽约，伊斯坦布尔

我们先建立三个分公司的数据库在上海的主数据库上，我们分别在三个分公司的数据库上建立对应的表，并且采用带有时区的字段。

同时我们分别在不同的数据库中，设置他们自己的时区。

ALTER DATABASE berlin SET timezone TO 'Europe/Berlin';

ALTER DATABASE newyork SET timezone TO 'America/New_York';

ALTER DATABASE istanbul SET timezone TO 'Asia/Istanbul';

此时我们在每个分公司在上海总部都具有自己的数据库，并且我们要在不同的数据库中使用当地的时间来进行时间的表达

1 我们分别在不同的数据库中，插入当地的时间

2 我们分别通过UTC 的时间，转换到当地的时间

3 我们通过本地的时间转换到当地的时间

第一个问题，我们在同一个实例的不同的数据库设置不同的时区，同时在这里输入同一个时间，并标定是类型是timestamp ,下面展示了相关的结果，可以看到这里的时间没有变化，与输入的值一致。说明如果你在不同的时区输入你认为的本地的时间，则他们会认为你输入的时间就是他们的本地时间。

2 我们将时间变为utc 的时间，并且再次插入看看是否能在插入时对日期数据插入后数值进行变化。我们变换方式，在插入的日期中变换格式，将日期标定为UTC 的时间。

show timezone;

truncate table time_test;

insert into time_test (id,time_zone) values (1,'2022-09-29 00:00:00'::timestamptz at time zone 'utc');

select * from time_test;

此时我们在输入时间后，到达实际的表中的数据已经不是UTC的时间而是当地的时间

3 我们输入的不是UTC 的时间，而是我们本地的时间，如上海时间，然后到不同的数据库中输入，看时间是否会变化。下面将时区变化为上海时间时区的简写，cst. 我们可以看到实际上在每个不同的分公司，会产生不同的当地的时间。

结论：在输入时间的过程中，如果你想将你当前时区的时间输入到其他时区的数据库中，并且以它所在的时区的时间进行显示，请使用 3 方法来输入数据。

本篇文字也向，海克斯科技CEO 辛吉飞 致敬 ，如今敢说实话揭露黑暗，得罪一个利益集团的人不多了。

注明：如果需要固定使用不使用麻烦的字段日期改变，可以使用如下的自建函数将时间进行变化

create function now_utc() returns timestamp as $$
  select now() at time zone 'utc';
$$ language sql;
create function now_utc() returns timestamptz as $$
  select now() at time zone 'utc';
$$ language sql;