PostgreSQL timestamp 字段转换时区
PostgreSQL About 2,141 words关键字
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)
Views: 686 · Posted: 2023-11-12
————        END        ————
Give me a Star, Thanks:)
https://github.com/fendoudebb/LiteNote扫描下方二维码关注公众号和小程序↓↓↓
Loading...