處理時區資訊

PostgreSQL 的時間序列分析

Jasmin Ludolf

Content Developer, DataCamp

時區資料

TIMESTAMP：沒有時區的日期時間
- 僅用單一時區時可用
- 皆以 UTC 表示
TIMESTAMPTZ：含時區的日期時間

Illustration of four clock faces with different times.

時區名稱

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|

此查詢：將時間轉換到目標時區，並移除時區標記

一起來練習吧！

PostgreSQL 的時間序列分析