在 PostgreSQL 時區轉換及計算時間

通常在使用資料表時，都會在每一筆紀錄上面寫入當下時間，而這個時間會根據目前系統所在的時區而有所不同，當然我們都會使用 UTC+0 作為標準時區，而欄位我們則會是使用 timestamp 或者是 unix time 格式，兩者最大的差異就是在前者 (timestamp) 會根據目前系統的時區來記錄，而後者 (unix time) 則是紀錄秒數差異 (Jan 01 1970) 而不會隨著系統時區改變而變化。如果是發展開源專案，則會使用後者居多，這樣不會因為使用者時區變化，而產生不同的差異，在 Gitea 開源專案保留了兩者，但是只要計算時間則是用 (unix time) 作轉換。

計算時區問題

針對底下兩個問題來看看該如何在 PostgreSQL 內計算時區，首先我們先定義在系統存放的時間都是統一 UTC+0 時區，而使用者查詢時的瀏覽器為台灣時間 Asia/Taipei 時區為 UTC+08:00，底下是第一個問題

查詢條件為當下時間過去 24 小時的全部紀錄

也就是說現在時間為 2018-09-02 15:00 那就是請抓取 2018-09-01 15:00 到 2018-09-02 15:00 區間內所有記錄，這個問題其實不難，跟時區也沒有任何關係，不管系統是存 UTC+0 或 UTC+8 都不影響。只要我們抓 now() 往前推算 24 小時即可。假設資料表有一個欄位為 created_at 存的是 timestamp 格式。底下就是解法:

1
2

select title, desc from users \
  where created_at > now() - interval '1 day'

其中 now() - interval '1 day' 代表著現在時間去減掉 1 天的時間。這邊沒有時區的問題，假設另一個問題如下:

請查詢過去 7 天的記錄 (含當下當天資料)

假設現在時間為 2018-09-02 16:00+08:00 (台灣時間星期天)，這時候我們預設的查詢時間範圍會是 2018-08-27 00:00+0800 到 2018-09-02 16:00+08:00 時間區間內所有資料，底下是目前資料庫的資料:

這時候使用上面的解法試試看:

1

where created_at  > now() - interval '6 day'

拿到底下資料

這時候你會發，怎麼 27 號的資料都沒有進來呢？原因出在 now() - interval '6 day' 計算出來的結果會是讀取時間大於 2018-08-27 16:00+08:00，那換算 UTC 時間則為 2018-08-27 08:00+00:00，這樣是不對的，那 8/27 該天的 00:00 ~ 08:00 的時間也沒被算進去，這時候需要時間的轉換

1

where created_at  > (now() - interval '6 day')::date

(now() - interval '6 day')::date 就可以把時間調整為當天 00:00 開始計算。這樣我們找出來的資料便是:

可以正確抓到 2018-08-27 的資料，但是看到這邊是不是又覺得怪怪的，最前面兩筆應該也要被算進來，我們先把上面的時區全部 +08:00

1
2

select created_at, \
  created_at at time zone 'UTC' at time zone 'Asia/Taipei'

有沒有發現第一筆跟第二筆，在台灣時間是在 08-27 號，所以理論上應該要是我們的查詢範圍之間，但是沒有被查到。解決方式就是將欄位都先轉成使用者時區再去做計算

1
2

created_at at time zone 'utc' time zone 'Asia/Taipei' > \
 (now() at time zone 'Asia/Taipei1' - interval '6 day')::date

其中關鍵點就在把 created_at 先轉 utc+0 再轉 utc+8 最後才做比較。

後記

使用者時區會隨在手機的所在地點做轉換，所以這邊的最好的作法就是，在資料庫統一存放 UTC+0 的時區，接著在 App 端登入帳號時，將使用者時區字串帶入到 Token 內，這樣使用者從台灣飛到美國時，登入 App 就能即時看到美國時區的資料。

See also

• MongoDB 效能調校紀錄
• 用 Docker 每天自動化備份 MySQL, Postgres 或 MongoDB 並上傳到 AWS S3
• 用 Postgres 計算員工上下班紀錄
• 將 Postgres 資料轉換到 CSV 格式
• [SQL] 如何從單一資料表取得每個 key 前 n 筆資料
• 在 Docker 偵測 MySQL 或 Postgres 是否啟動