在 PostgreSQL 時區轉換及計算時間

2000px-Postgresql_elephant.svg 通常在使用資料表時,都會在每一筆紀錄上面寫入當下時間,而這個時間會根據目前系統所在的時區而有所不同,當然我們都會使用 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:002018-09-02 15:00 區間內所有記錄,這個問題其實不難,跟時區也沒有任何關係,不管系統是存 UTC+0 或 UTC+8 都不影響。只要我們抓 now() 往前推算 24 小時即可。假設資料表有一個欄位為 created_at 存的是 timestamp 格式。底下就是解法:
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+08002018-09-02 16:00+08:00 時間區間內所有資料,底下是目前資料庫的資料:
id created_at (utc+0)
1231 2018-08-26 18:25:35.624
1225 2018-08-26 19:15:19.187
1220 2018-08-27 04:24:59.306
1222 2018-08-27 05:38:57.174
1230 2018-08-27 07:21:35.897
1239 2018-08-28 07:37:52.345
1264 2018-08-30 05:21:17.157
1290 2018-08-31 12:05:04.764
1356 2018-08-31 20:51:29.784
1358 2018-09-01 12:14:13.118
1355 2018-09-01 19:21:36.482
1354 2018-09-02 03:18:38.626
1361 2018-09-02 03:37:05.171
這時候使用上面的解法試試看:
where created_at  > now() - interval '6 day'
拿到底下資料
id created_at (utc+0)
1239 2018-08-28 07:37:52.345
1264 2018-08-30 05:21:17.157
1290 2018-08-31 12:05:04.764
1356 2018-08-31 20:51:29.784
1358 2018-09-01 12:14:13.118
1355 2018-09-01 19:21:36.482
1354 2018-09-02 03:18:38.626
1361 2018-09-02 03:37:05.171
這時候你會發,怎麼 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 的時間也沒被算進去,這時候需要時間的轉換
where created_at  > (now() - interval '6 day')::date
(now() - interval '6 day')::date 就可以把時間調整為當天 00:00 開始計算。這樣我們找出來的資料便是:
id created_at (utc+0)
1220 2018-08-27 04:24:59.306
1222 2018-08-27 05:38:57.174
1230 2018-08-27 07:21:35.897
1239 2018-08-28 07:37:52.345
1264 2018-08-30 05:21:17.157
1290 2018-08-31 12:05:04.764
1356 2018-08-31 20:51:29.784
1358 2018-09-01 12:14:13.118
1355 2018-09-01 19:21:36.482
1354 2018-09-02 03:18:38.626
1361 2018-09-02 03:37:05.171
可以正確抓到 2018-08-27 的資料,但是看到這邊是不是又覺得怪怪的,最前面兩筆應該也要被算進來,我們先把上面的時區全部 +08:00
select created_at, \
  created_at at time zone 'UTC' at time zone 'Asia/Taipei'
created_at (utc+0) created_at (utc+8)
2018-08-26 18:25:35.624 2018-08-27 02:25:35.624
2018-08-26 19:15:19.187 2018-08-27 03:15:19.187
2018-08-27 04:24:59.306 2018-08-27 12:24:59.306
2018-08-27 05:38:57.174 2018-08-27 13:38:57.174
2018-08-27 07:21:35.897 2018-08-27 15:21:35.897
2018-08-28 07:37:52.345 2018-08-28 15:37:52.345
2018-08-30 05:21:17.157 2018-08-30 13:21:17.157
2018-08-31 12:05:04.764 2018-08-31 20:05:04.764
2018-08-31 20:51:29.784 2018-09-01 04:51:29.784
2018-09-01 12:14:13.118 2018-09-01 20:14:13.118
2018-09-01 19:21:36.482 2018-09-02 03:21:36.482
2018-09-02 03:18:38.626 2018-09-02 11:18:38.626
2018-09-02 03:37:05.171 2018-09-02 11:37:05.171
有沒有發現第一筆跟第二筆,在台灣時間是在 08-27 號,所以理論上應該要是我們的查詢範圍之間,但是沒有被查到。解決方式就是將欄位都先轉成使用者時區再去做計算
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 就能即時看到美國時區的資料。