PostgreSQL timestamp 字段转换时区

关键字

at time zone

查看当前数据库默认时区

显示UTC时区。

show time zone;

输出

lite_note=# show time zone;
   TimeZone    
---------------
 Asia/Shanghai
(1 row)

转换 SQL

查看时间戳字段

select current_timestamp;

输出

lite_note=# select current_timestamp;
       current_timestamp       
-------------------------------
 2023-11-12 10:31:47.953034+08
(1 row)

转换成UTC时区，转换后将变为不带时区的timestamp类型

select current_timestamp at time zone 'UTC';

输出

lite_note=# select current_timestamp, current_timestamp at time zone 'UTC';
       current_timestamp       |          timezone          
-------------------------------+----------------------------
 2023-11-12 10:59:37.562292+08 | 2023-11-12 02:59:37.562292
(1 row)

备注

转换时区时，可以写时区的完整名称，也可以写时区简写和时区偏移量。

select current_timestamp, 
       current_timestamp at time zone 'Australia/Sydney' as sydney, 
       current_timestamp at time zone 'UTC' as utc, 
       current_timestamp at time zone '0' as abbrev;

输出

lite_note=# select current_timestamp, current_timestamp at time zone 'Australia/Sydney' as sydney, current_timestamp at time zone 'UTC' as utc, current_timestamp at time zone '0' as abbrev;
       current_timestamp       |           sydney           |            utc             |           abbrev           
-------------------------------+----------------------------+----------------------------+----------------------------
 2023-11-12 11:07:32.366429+08 | 2023-11-12 14:07:32.366429 | 2023-11-12 03:07:32.366429 | 2023-11-12 03:07:32.366429
(1 row)

查看所有时区

时区简写、与UTC时区偏移量

lite_note=# select * from pg_timezone_abbrevs limit 5;
 abbrev | utc_offset | is_dst 
--------+------------+--------
 ACDT   | 10:30:00   | t
 ACSST  | 10:30:00   | t
 ACST   | 09:30:00   | f
 ACT    | -05:00:00  | f
 ACWST  | 08:45:00   | f
(5 rows)

时区名称、时区简写、与UTC时区偏移量

lite_note=# select * from pg_timezone_names limit 5;
       name       | abbrev | utc_offset | is_dst 
------------------+--------+------------+--------
 Indian/Mauritius | +04    | 04:00:00   | f
 Indian/Chagos    | +06    | 06:00:00   | f
 Indian/Mayotte   | EAT    | 03:00:00   | f
 Indian/Christmas | +07    | 07:00:00   | f
 Indian/Cocos     | +0630  | 06:30:00   | f
(5 rows)