处理时区信息

PostgreSQL 中的时间序列分析

Jasmin Ludolf

Content Developer, DataCamp

时区数据

TIMESTAMP：不含时区的日期时间
- 仅在使用单一时区时适用
- 所有时间为 UTC
TIMESTAMPTZ：含时区的日期时间

四个时钟显示不同时间的插图。

时区名称

SELECT * FROM pg_timezone_names;

| name             |abbrev|utc_offset|is_dst|
|------------------|------|----------|------|
|Africa/Abidjan    |GMT   |  00:00:00|false |
|Africa/Accra      |GMT   |  00:00:00|false |
|Africa/Addis_Ababa|EAT   |   3:00:00|false |
...

is_dst：该地点是否实施夏令时

查看当前时区

SHOW TIMEZONE;

|TimeZone       |
|---------------|
|Europe/Brussels|

表中的时区数据

datetimes 表包含两个字段：
- datetime：TIMESTAMP
- datetimetz：TIMESTAMPTZ

INSERT INTO datetimes (datetime, datetimetz)
VALUES('2023-01-03 12:00:00','2023-01-03 12:00:00');

|datetime           |datetimetz               |
|-------------------|-------------------------|
|2023-01-03 12:00:00|2023-01-03 12:00:00+01:00|

验证数据类型

pg_typeof(field_name)：检查字段的数据类型

SELECT
    pg_typeof(datetime) AS "type of(datetime)", 
    pg_typeof(datetimetz) AS "type of(datetimetz)"
FROM datetimes;

|type of(datetime)          |type of(datetimetz)     |
|---------------------------|------------------------|
|timestamp without time zone|timestamp with time zone|

使用时区信息

INSERT INTO datetimes (datetime, datetimetz)
VALUES('2023-01-03 12:00:00+00','2023-01-03 12:00:00+00');

SELECT * FROM datetimes;

|datetime           |datetimetz               |
|-------------------|-------------------------|
|2023-01-03 12:00:00|2023-01-03 12:00:00+01:00|
|2023-01-03 12:00:00|2023-01-03 13:00:00+01:00|

添加时区信息

AT TIME ZONE：添加、变更或移除时区信息
- 在 TIMESTAMP 与 TIMESTAMPTZ 之间转换

添加时区：

SELECT 
    TIMESTAMP '2020-12-31 23:59:59' AT TIME ZONE 'Europe/London' AS added;

|added                    |
|-------------------------|
|2021-01-01 00:59:59+01:00|

此查询：按给定时区解释时间戳，生成 TIMESTAMPTZ，并按默认显示

变更与移除时区

SELECT 
    '2020-12-31 23:59:59+00'::TIMESTAMPTZ AT TIME ZONE 'Europe/Paris' 
    AS shifted;

|shifted            |
|-------------------|
|2021-01-01 00:59:59|

此查询：将时间戳转换到目标时区，并移除时区标记

Passons à la pratique !

PostgreSQL 中的时间序列分析