處理時區：TIMEZONE

假設你有個訂機票的 app。航班從紐約當地時間 10:00 起飛，到倫敦當地時間 22:00 抵達。如果你沒考慮時區，你的 server 可能會搞得一團亂，顯示錯誤的抵達時間。

時區這東西，有時是你最好的朋友（但有時也會讓你抓狂，尤其出包的時候）。如果你的用戶分布在不同國家，或是你要處理跟當地時間有關的排程（像是航班時間或活動表），那時區就超級重要啦。

時間資料型別

我們之前聊過，處理時間戳記有兩種資料型別：

• TIMESTAMP：日期和時間不含時區資訊。
• TIMESTAMPTZ：日期和時間有時區資訊。

我們再用個例子來說明一下。

-- 建一個有兩個欄位的表格：TIMESTAMP 和 TIMESTAMPTZ
CREATE TABLE flight_schedule (
    flight_id SERIAL PRIMARY KEY,
    departure_time TIMESTAMP,
    departure_time_with_tz TIMESTAMPTZ
);

-- 插入資料
INSERT INTO flight_schedule (departure_time, departure_time_with_tz)
VALUES
    ('2023-10-25 10:00:00', '2023-10-25 10:00:00+00');

-- 查詢資料
SELECT * FROM flight_schedule;

結果會依你 server 的時區而不同。舉例來說：

重點差異：

• departure_time 欄位只存日期和時間，沒有任何時區資訊。
• departure_time_with_tz 欄位則會把時區資訊一起存起來（這裡是 +00）。

時間在不同時區間的轉換

在 PostgreSQL 裡，處理時區會用到 AT TIME ZONE 這個 function。

把 UTC 轉成當地時間

假設我們有一個 UTC（世界協調時間）的時間戳，要顯示給在 America/New_York 時區的用戶看。

SELECT
    '2023-10-25 14:00:00+00'::TIMESTAMPTZ AT TIME ZONE 'America/New_York' AS local_time;

結果：

AT TIME ZONE 就像魔法一樣，把 UTC 的時間轉成你指定的時區。

把當地時間轉成 UTC

現在反過來：我們有 America/New_York 的時間，要轉成 UTC。

SELECT
    '2023-10-25 10:00:00'::TIMESTAMP AT TIME ZONE 'America/New_York' AS utc_time;

結果：

注意，這個結果會是 TIMESTAMPTZ 格式，因為它包含了時區資訊（這裡是 UTC）。

操作 TIMESTAMPTZ 資料型別

你在用 TIMESTAMPTZ 的時候，PostgreSQL 會自動考慮你 server（或你自己設定的）時區。

你可以用這個指令設定目前 session 的時區：

SET TIMEZONE = 'Europe/Istanbul';

這樣之後所有 TIMESTAMPTZ 的操作都會用這個時區來處理。

範例：插入和查詢資料

-- 設定時區
SET TIMEZONE = 'Europe/Istanbul';

-- 插入資料
INSERT INTO flight_schedule (departure_time_with_tz)
VALUES ('2023-10-25 10:00:00+00');

-- 查詢資料
SELECT departure_time_with_tz FROM flight_schedule;

在 Europe/Istanbul 時區下的結果：

PostgreSQL 會自動把 UTC 的時間轉成你指定的時區。

實戰範例

排程考慮時區。 假設我們有一個航班排程表，每筆資料都存 UTC 的出發時間。我們想要把每個航班的出發時間轉成當地時間顯示。

SELECT
    flight_id,
    departure_time_with_tz AT TIME ZONE 'America/New_York' AS local_time
FROM flight_schedule;

比較不同時區的時間資料。 假設我們要比較兩個在不同城市發生的事件。PostgreSQL 會自動把資料轉成同一個時區來比較。

SELECT
    '2023-10-25 10:00:00+03'::TIMESTAMPTZ > '2023-10-25 07:00:00+00'::TIMESTAMPTZ AS event_one_later;

結果：

比較結果是 true，因為 10:00+03 跟 07:00+00 是同一個時間。

小撇步跟常見地雷

處理時間真的很容易踩雷，常見問題有：

• 用 TIMESTAMP 取代 TIMESTAMPTZ，然後發現時間都對不起來——因為根本沒考慮時區。
• 搞不清楚 server 的時區，結果資料寫進去一個時間，查出來又變另一個。
• AT TIME ZONE 用錯時區名稱，結果不是錯誤就是時間怪怪的。

避免踩雷的方法：

• 幾乎都用 TIMESTAMPTZ，尤其資料會受時區影響的時候。
• 時間都存 UTC，顯示時再轉成用戶要的時區。
• 想更深入可以看 PostgreSQL 官方時間與時區文件 —— 超實用！