PostgreSQL 时区整理
PostgreSQL 时区整理
1. 时区说明：
TIMESTAMP '2004-10-19 10:23:54+08' 表示当前客户端所在时区时间是：2004-10-19 10:23:54，时区是：+8时区，当前时间减去8就是UTC时间。
1）timestamp：
The SQL standard differentiates timestamp without time zone and timestamp with time zone literals by the presence of a “+” or “-” symbol and time zone offset after the time. Hence, according to the standard,
TIMESTAMP '2004-10-19 10:23:54'
is a timestamp without time zone, while
TIMESTAMP '2004-10-19 10:23:54+02'
is a timestamp with time zone. PostgreSQL never examines the content of a literal string before determining its type, and therefore will treat both of the above as timestamp without time zone. To ensure that a literal is treated as timestamp with time zone, give it the correct explicit type:
TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'
In a literal that has been determined to be timestamp without time zone, PostgreSQL will silently ignore any time zone indication. That is, the resulting value is derived from the date/time fields in the input value, and is not adjusted for time zone.
For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT).
An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system's TimeZone parameter, and is converted to UTC using the offset for the timezone zone.
When a timestamp with time zone value is output, it is always converted from UTC to the current timezone zone, and displayed as local time in that zone. To see the time in another time zone, either change timezone or use the AT TIME ZONE construct (see Section 9.9.4).
带时区的时间戳，内部存储始终是UTC格式（postgresql.conf中timezone并不控制数据库存储的时间格式）。
Conversions between timestamp without time zone and timestamp with time zone normally assume that the timestamp without time zone value should be taken or given as timezone local time. A different time zone can be specified for the conversion using AT TIME ZONE.
https://www.postgresql.org/docs/16/datatype-datetime.html
2）AT TIME ZONE：
The AT TIME ZONE operator converts time stamp without time zone to/from time stamp with time zone, and time with time zone values to different time zones. Table 9.34 shows its variants.
timestamp without time zone AT TIME ZONE zone → timestamp with time zone
Converts given time stamp without time zone to time stamp with time zone, assuming the given value is in the named time zone.
timestamp '2001-02-16 20:38:40' at time zone 'America/Denver' → 2001-02-17 03:38:40+00
timestamp with time zone AT TIME ZONE zone → timestamp without time zone
Converts given time stamp with time zone to time stamp without time zone, as the time would appear in that zone.
timestamp with time zone '2001-02-16 20:38:40-05' at time zone 'America/Denver' → 2001-02-16 18:38:40
time with time zone AT TIME ZONE zone → time with time zone
Converts given time with time zone to a new time zone. Since no date is supplied, this uses the currently active UTC offset for the named destination zone.
time with time zone '05:34:17-05' at time zone 'UTC' → 10:34:17+00
In these expressions, the desired time zone zone can be specified either as a text value (e.g., 'America/Los_Angeles') or as an interval (e.g., INTERVAL '-08:00'). In the text case, a time zone name can be specified in any of the ways described in Section 8.5.3. The interval case is only useful for zones that have fixed offsets from UTC, so it is not very common in practice.
Examples (assuming the current TimeZone setting is America/Los_Angeles):
SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'America/Denver';
Result: 2001-02-16 19:38:40-08
SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'America/Denver';
Result: 2001-02-16 18:38:40
SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'Asia/Tokyo' AT TIME ZONE 'America/Chicago';
Result: 2001-02-16 05:38:40
The first example adds a time zone to a value that lacks it, and displays the value using the current TimeZone setting. The second example shifts the time stamp with time zone value to the specified time zone, and returns the value without a time zone. This allows storage and display of values different from the current TimeZone setting. The third example converts Tokyo time to Chicago time.
The function timezone(zone, timestamp) is equivalent to the SQL-conforming construct timestamp AT TIME ZONE zone.
https://www.postgresql.org/docs/16/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT
3）数据库配置：
TimeZone (string)
Sets the time zone for displaying and interpreting time stamps. The built-in default is GMT, but that is typically overridden in postgresql.conf; initdb will install a setting there corresponding to its system environment. See Section 8.5.3 for more information.
默认读取本地时区，我本地是Asia/Shanghai。
2. 数据库的时区查询：
常用的时区：
"PRC" 是中国的标准时间，代表 "People's Republic of China"。PRC时区的偏差是UTC+8，它距离世界协调时间（UTC）向东八个小时，因此在PRC时区的地区，当UTC时间为下午12点时，当地时间为下午8点。
ROC代表中华民国时区，也称为台湾时区。它是相对于UTC+8的时区，覆盖了台湾、澎湖、金门、马祖等地区。在日常生活中，台湾通常使用ROC时区来表示时间。
UTC代表世界协调时间，是国际标准时间，传统上称为格林威治标准时间，GMT。UTC不是基于任何特定的国家或地区，而是作为全球标准时间被广泛接受。其他时区相对于UTC可以是早于UTC（例如UTC-5，表示比UTC早5个小时）或晚于UTC（例如UTC+3，表示比UTC晚3个小时）。UTC通常用于协调全球定时，例如在航空、天文学、气象学和计算机网络中。
"Etc/UTC" 是一个时区标识符，它表示的是协调世界时（UTC），也被称为格林尼治标准时间（GMT），是世界标准的时间基准。这个时区标识符在计算机系统中常用于标识与UTC时间匹配的时间戳或日期时间值。
"Etc" 是 "etcetera" 的缩写，表示 "其它"，在时区标识符中表示一些不太常见的时区，包括UTC、GMT和一些反向的时区，如Etc/GMT+1表示UTC-1的时区。因此，“Etc/UTC”代表的是UTC时区本身。
Etc/UTC | UTC | 00:00:00 | f
Etc/UTC
Asia/Shanghai | CST | 08:00:00 | f
Asia/Hong_Kong | HKT | 08:00:00 | f
Asia/Tokyo | JST | 09:00:00 | f
数据库查询：
select * from pg_timezone_names;
pithe=# select * from pg_timezone_names where name like '%UTC%';
name | abbrev | utc_offset | is_dst
---------+--------+------------+--------
Etc/UTC | UTC | 00:00:00 | f
UTC | UTC | 00:00:00 | f
(2 rows)
pithe=# select * from pg_timezone_names where name like '%Asia%' order by utc_offset ;
name | abbrev | utc_offset | is_dst
--------------------+--------+------------+--------
Asia/Harbin | CST | 08:00:00 | f
Asia/Macau | CST | 08:00:00 | f
Asia/Macao | CST | 08:00:00 | f
Asia/Kuching | +08 | 08:00:00 | f
Asia/Irkutsk | +08 | 08:00:00 | f
Asia/Ulaanbaatar | +08 | 08:00:00 | f
Asia/Brunei | +08 | 08:00:00 | f
Asia/Ulan_Bator | +08 | 08:00:00 | f
Asia/Chongqing | CST | 08:00:00 | f
Asia/Ujung_Pandang | WITA | 08:00:00 | f
Asia/Shanghai | CST | 08:00:00 | f
Asia/Chungking | CST | 08:00:00 | f
Asia/Makassar | WITA | 08:00:00 | f
Asia/Singapore | +08 | 08:00:00 | f
Asia/Choibalsan | +08 | 08:00:00 | f
Asia/Kuala_Lumpur | +08 | 08:00:00 | f
Asia/Hong_Kong | HKT | 08:00:00 | f
Asia/Manila | PST | 08:00:00 | f
Asia/Taipei | CST | 08:00:00 | f
Asia/Tokyo | JST | 09:00:00 | f
Asia/Chita | +09 | 09:00:00 | f
Asia/Yakutsk | +09 | 09:00:00 | f
Asia/Jayapura | WIT | 09:00:00 | f
Asia/Khandyga | +09 | 09:00:00 | f
Asia/Pyongyang | KST | 09:00:00 | f
Asia/Dili | +09 | 09:00:00 | f
Asia/Seoul | KST | 09:00:00 | f
pithe=# select * from pg_timezone_names where utc_offset = '08:00:00';
name | abbrev | utc_offset | is_dst
--------------------+--------+------------+--------
Australia/Perth | AWST | 08:00:00 | f
Australia/West | AWST | 08:00:00 | f
Etc/GMT-8 | +08 | 08:00:00 | f
Asia/Shanghai | CST | 08:00:00 | f
Asia/Ulan_Bator | +08 | 08:00:00 | f
Asia/Brunei | +08 | 08:00:00 | f
Asia/Ulaanbaatar | +08 | 08:00:00 | f
Asia/Kuching | +08 | 08:00:00 | f
Asia/Manila | PST | 08:00:00 | f
Asia/Taipei | CST | 08:00:00 | f
Asia/Macau | CST | 08:00:00 | f
Asia/Macao | CST | 08:00:00 | f
Asia/Irkutsk | +08 | 08:00:00 | f
Asia/Chongqing | CST | 08:00:00 | f
Asia/Ujung_Pandang | WITA | 08:00:00 | f
Asia/Makassar | WITA | 08:00:00 | f
Asia/Singapore | +08 | 08:00:00 | f
Asia/Harbin | CST | 08:00:00 | f
Asia/Kuala_Lumpur | +08 | 08:00:00 | f
Asia/Hong_Kong | HKT | 08:00:00 | f
Asia/Choibalsan | +08 | 08:00:00 | f
Asia/Chungking | CST | 08:00:00 | f
ROC | CST | 08:00:00 | f
Singapore | +08 | 08:00:00 | f
Hongkong | HKT | 08:00:00 | f
PRC | CST | 08:00:00 | f
(26 rows)
3 验证测试
1） 表结构及插入数据：
create table test_tz(id int, crt_time timestamp, crt_time_tz timestamptz);
insert into test_tz values(1, '2023-10-08 12:00:21.405985+08', '2023-10-08 12:00:21.405985');
insert into test_tz values(2, '2023-10-08 12:00:21.405985+08', '2023-10-08 12:00:21.405985+8');
2）时区设置：
- 操作系统：
pithe@PITHE-MB0 data % sudo systemsetup -gettimezone
Time Zone: Asia/Shanghai
- server：
postgresql.conf中配置默认跟随操作系统的时区为：Asia/Shanghai，手动改为：UTC，并重启生效
- client1：
psql默认会跟随server端的时区，没有获取本地操作系统的时区，这里手动设置为：Asia/Shanghai：
pithe=# show timezone;
TimeZone
----------
Etc/UTC
(1 row)
pithe=#
pithe=# set timezone = 'Asia/Shanghai';
SET
pithe=# show timezone;
TimeZone
----------------
Asia/Shanghai
(1 row)
- client2:
psql默认会跟随server端的时区，没有获取本地操作系统的时区，这里是：Etc/UTC
pithe=# show timezone;
TimeZone
----------
Etc/UTC
(1 row)
验证1：数据库端和客户端之间时区的转换
-
client2: 由于client和server都是UTC时区
第一条数据：带有时区信息+8，存储到数据库中时间会转为UTC时区，-8小时，为：“2023-10-08 04:00:21.405985+00”
第二条数据：没有带有时区信息，默认为就是client当前的时区信息，也就是UTC，因此存储的时候不做转换，还是：“2023-10-08 12:00:21”
测试数据如下：
pithe@PITHE-MB0 data % psql
psql (16beta1)
Type "help" for help.
pithe=# show timezone;
TimeZone
----------
Etc/UTC
(1 row)
pithe=# insert into test_tz values(1, '2023-10-08 12:00:21.405985+08', '2023-10-08 12:00:21.405985+08');
INSERT 0 1
pithe=# select * from test_tz ;
id | crt_time | crt_time_tz
----+----------------------------+-------------------------------
1 | 2023-10-08 12:00:21.405985 | 2023-10-08 04:00:21.405985+00
(1 row)
pithe=# insert into test_tz values(2, '2023-10-08 12:00:21.405985+08', '2023-10-08 12:00:21.405985');
INSERT 0 1
pithe=# select * from test_tz ;
id | crt_time | crt_time_tz
----+----------------------------+-------------------------------
1 | 2023-10-08 12:00:21.405985 | 2023-10-08 04:00:21.405985+00
2 | 2023-10-08 12:00:21.405985 | 2023-10-08 12:00:21.405985+00
(2 rows)
- client1：
设置时区为Asia/Shanghai，查询数据时，会从Server的UTC +8小时进行输出。
第一条数据就跟插入的时候一样（插-8，查+8）： '2023-10-08 12:00:21.405985+08'。
第二条数据在原来基础上+8小时为：“2023-10-08 20:00:21.405985+08”。
pithe@PITHE-MB0 data % psql
psql (16beta1)
Type "help" for help.
pithe=# show timezone;
TimeZone
----------
Etc/UTC
(1 row)
pithe=# set timezone = 'Asia/Shanghai';
SET
pithe=# show timezone;
TimeZone
---------------
Asia/Shanghai
(1 row)
pithe=# select * from test_tz ;
id | crt_time | crt_time_tz
----+----------------------------+-------------------------------
1 | 2023-10-08 12:00:21.405985 | 2023-10-08 12:00:21.405985+08
2 | 2023-10-08 12:00:21.405985 | 2023-10-08 20:00:21.405985+08
(2 rows)
验证2：改变postgresql.conf中时区，已经保存的数据不受影响（这也是数据库端存储为UTC的智慧所在）
1）改变Server的时区为Asia/Shanghai
2）client1：时区始终是Shanghai，因此查询不变化
2）client2：由于client默认时区跟随server，server时区变为了Shanghai，因此client也是Shanghai，查询数据就跟之前有变化，都+8小时，但时间后面跟了时区，能清晰的看到数据是一样的：
pithe=# select * from test_tz ;
id | crt_time | crt_time_tz
----+----------------------------+-------------------------------
1 | 2023-10-08 12:00:21.405985 | 2023-10-08 04:00:21.405985+00
2 | 2023-10-08 12:00:21.405985 | 2023-10-08 12:00:21.405985+00
(2 rows)
pithe=# \q
pithe@PITHE-MB0 data % psql
psql (16beta1)
Type "help" for help.
pithe=# select * from test_tz ;
id | crt_time | crt_time_tz
----+----------------------------+-------------------------------
1 | 2023-10-08 12:00:21.405985 | 2023-10-08 12:00:21.405985+08
2 | 2023-10-08 12:00:21.405985 | 2023-10-08 20:00:21.405985+08
(2 rows)
设置客户端为之前的时区，查询数据就一致了，因为都是UTC时间。（也佐证了server端就不会以Shanghai的时区为存储数据，否则下面的查询应该是要-8。server是+8时区，客户端是UTC时区，返回客户端要-8，此时没有-8，说明server端即使设置了timezone是+8时区，存储的还是UTC时区）：
pithe=# set timezone = 'UTC';
SET
pithe=# select * from test_tz ;
id | crt_time | crt_time_tz
----+----------------------------+-------------------------------
1 | 2023-10-08 12:00:21.405985 | 2023-10-08 04:00:21.405985+00
2 | 2023-10-08 12:00:21.405985 | 2023-10-08 12:00:21.405985+00
(2 rows)
验证3：数据库端存储始终是UTC时区时间：
改变server为Shanghai，client2是UTC，插入两条数据，一条使用客户端的时区，一条指定时区+8。client2查询插入的数据：
第一条使用客户端UTC时区，直接存储；第二条使用指定的时区，转到UTC需要-8小时。
pithe=# show timezone;
TimeZone
---------------
Asia/Shanghai
(1 row)
pithe=# set timezone = 'Etc/UTC';
SET
pithe=# show timezone;
TimeZone
----------
Etc/UTC
(1 row)
pithe=#
pithe=# insert into test_tz values(1, '2023-10-08 12:00:21.405985+08', '2023-10-08 12:00:21.405985');
INSERT 0 1
pithe=# insert into test_tz values(2, '2023-10-08 12:00:21.405985+08', '2023-10-08 12:00:21.405985+8');
INSERT 0 1
pithe=# select * from test_tz ;
id | crt_time | crt_time_tz
----+----------------------------+-------------------------------
1 | 2023-10-08 12:00:21.405985 | 2023-10-08 12:00:21.405985+00
2 | 2023-10-08 12:00:21.405985 | 2023-10-08 04:00:21.405985+00
(2 rows)
clint1是Shanghai，查询的是+8的时间：
两条需要从UTC转为Shanghai，时间+8小时，第二条插入的时候-8小时，此时再+8小时，跟插入的数据就一样。
pithe=# show timezone;
TimeZone
---------------
Asia/Shanghai
(1 row)
pithe=# select * from test_tz ;
id | crt_time | crt_time_tz
----+----------------------------+-------------------------------
1 | 2023-10-08 12:00:21.405985 | 2023-10-08 20:00:21.405985+08
2 | 2023-10-08 12:00:21.405985 | 2023-10-08 12:00:21.405985+08
(2 rows)
验证4：如果插入的时间指定了时区，就不会读取客户端的时区信息：
SET
pithe=# truncate test_tz ;
TRUNCATE TABLE
pithe=# insert into test_tz values(3, '2023-10-08 12:00:21.405985+08', '2023-10-08 12:00:21.405985+8');
INSERT 0 1
pithe=# select * from test_tz;
id | crt_time | crt_time_tz
----+----------------------------+-------------------------------
3 | 2023-10-08 12:00:21.405985 | 2023-10-08 12:00:21.405985+08
(1 row)
pithe=# insert into test_tz values(4, '2023-10-08 12:00:21.405985+08', '2023-10-08 12:00:21.405985');
INSERT 0 1
pithe=# select * from test_tz;
id | crt_time | crt_time_tz
----+----------------------------+-------------------------------
3 | 2023-10-08 12:00:21.405985 | 2023-10-08 12:00:21.405985+08
4 | 2023-10-08 12:00:21.405985 | 2023-10-08 12:00:21.405985+08
(2 rows)
pithe=# insert into test_tz values(5, '2023-10-08 12:00:21.405985+08', '2023-10-08 12:00:21.405985+9');
INSERT 0 1
pithe=# select * from test_tz;
id | crt_time | crt_time_tz
----+----------------------------+-------------------------------
3 | 2023-10-08 12:00:21.405985 | 2023-10-08 12:00:21.405985+08
4 | 2023-10-08 12:00:21.405985 | 2023-10-08 12:00:21.405985+08
5 | 2023-10-08 12:00:21.405985 | 2023-10-08 11:00:21.405985+08
(3 rows)
4. 验证总结：
因此，时区这里有以下几个特点：
-
initdb的时候postgresql.conf中timezone是跟随操作系统的时区进行设置的。
-
postgresql.conf中设置的timezone，更像是client的时区，有些客户端在创建链接时并不会读取客户端本地的时区信息，例如psql，有些客户端会读取本地的时区信息进行覆盖，例如java。
-
插入时，如果插入的时间指定了时区，就不会读取客户端的时区信息，存储到数据库时会根据插入的时间+时区，换算为UTC时区的时间。
-
查询时，会将UTC时区的时间，转换为客户端所在时区的时间进行显示。像pgadmin和java程序，会读取本地的时区，对客户端进行时区设置。psql则直接使用server的timezone配置。
-
插入的数据转为UTC时区存储到数据库，这样能保证不管时区怎么变化，插入的数据不会受影响，不会出现数据错误。
5. 那么如果postgresql.conf文件中的timezone配置改变了有什么影响？
-
类似psql这类客户端，如果server端时区改变了，client时区跟着改变，查询的时间可能就变化了。但时间有时区标记，时间本身不会错，只是可能会忽略时区的标记，以为数据错了。例如：插入“2023-10-08 12:00:21.405985+08” 和查询的“2023-10-08 11:00:21.405985+07”时间是同一个，但如果忽略时区，可能认为数据不一致。
-
client如果设置了时区，例如java程序，即使server端改变了时区，由于数据库存储的还是UTC时间，客户端时区也没有变化（java类的应用程序，会覆盖为本地的时区），因此显示的数据也不会变。
6. 最佳实践：
-
插入和查询都不设置时区，都以server端为准。
-
插入和查询都设置时区，都以自己的为准。
-
插入和查询都不用timestamptz类型，相当于都用UTC时区时间。
-
查询都统一转为UTC时间格式，PG提供了类似的函数：
pithe=# select timezone('UTC', '2023-10-08 12:00:21.405985+08');
timezone
----------------------------
2023-10-08 04:00:21.405985
(1 row)
- java程序中处理timestamptz时间时，就如实返回，反正带有时区的：
插入“2023-10-08 12:00:21.405985+08” 和查询的“2023-10-08 11:00:21.405985+07”时间是同一个，但如果忽略时区，可能认为数据不一致。
JDBC在连接数据库时，会使用JVM默认的时区设置。如果没有显式设置时区，JDBC会默认使用JVM的时区设置。因此，如果JVM的时区设置为本地时区，那么JDBC连接数据库时也会使用本地时区。
但是，为了避免时区带来的问题，建议在连接数据库时显式设置时区，以确保使用一致的时区。可以使用JDBC提供的setTimeZone()方法来设置时区，例如：
String url = "jdbc:postgresql://localhost/postgres";
String user = "postgres";
String password = "xxxx";
TimeZone timeZone = TimeZone.getTimeZone("Asia/Shanghai");
Properties props = new Properties();
props.setProperty("user", user);
props.setProperty("password", password);
props.setProperty("serverTimezone", timeZone.getID());
Connection conn = DriverManager.getConnection(url, props);
在上述代码中，我们显式设置时区为"Asia/Shanghai"，并将其设置为连接属性的"serverTimezone"值。这样，JDBC连接数据库时就会使用指定的时区。
