Postgrsql Timezone 及時間戳記資料型態

今天來探討時區跟小伙伴們.

首先來看怎樣查現在 Server 設定的時區.

select current_setting('TIMEZONE');
+-----------------+
| current_setting |
+-----------------+
| Asia/Taipei     |
+-----------------+

show timezone;
+-------------+
|  TimeZone   |
+-------------+
| Asia/Taipei |
+-------------+

查現在時區與 UTC 的 offset

select extract(timezone from now()) / 3600 as "utc_offset"
     , date_part('timezone', now()) / 3600 as "utc_offset_2";

+------------+--------------+
| utc_offset | utc_offset_2 |
+------------+--------------+
|          8 |            8 |
+------------+--------------+

我們可以透過 pg_catalog.pg_timezone_names 查詢時區相關資料

show intervalstyle;
+---------------+
| IntervalStyle |
+---------------+
| iso_8601      |
+---------------+

select *
  from pg_catalog.pg_timezone_names
 where name ilike '%taipei%';
 
+-------------+--------+------------+--------+
|    name     | abbrev | utc_offset | is_dst |
+-------------+--------+------------+--------+
| Asia/Taipei | CST    | PT8H       | f      |
+-------------+--------+------------+--------+

在 Postgresql 內含的時區資料中, Asia/Taipei 對應的縮寫是CST,UTC偏移使用
iso_8601 表示為 8小時, is_dst 代表是否 日光節約時間.

也可以查詢 pg_timezone_abbrevs 獲得一些時區縮寫的相關資料

select utc_offset, is_dst
     , string_agg(abbrev, ' ' order by abbrev) as abbrevs
     , count(*) as cnt
  from pg_timezone_abbrevs
 group by utc_offset, is_dst
 order by utc_offset, is_dst;

實際上有61筆,僅列出 標準時區 
+-[ RECORD 23 ]--------------------------+
| utc_offset | PT0S                      |
| is_dst     | f                         |
| abbrevs    | GMT UCT UT UTC WET Z ZULU |
| cnt        | 7                         |

接著來探討 timestamp 與 timestamp with time zone 這兩個資料型態.

select pg_column_size(timestamp without time zone 'now')
     , pg_column_size(timestamp with time zone 'now');
+----------------+----------------+
| pg_column_size | pg_column_size |
+----------------+----------------+
|              8 |              8 |
+----------------+----------------+

兩種型態都是 8 bytes.

timestamp  相信大家比較熟悉,以下搭配 timestamp with time zone 
做一些輸入的方式介紹

為了舉例方便,查詢兩個台北附近,有點熱的城市,看她們的時區.

select *  
  from pg_catalog.pg_timezone_names
 where name ~* 'tokyo|Ho_Chi_Minh';

+------------------+--------+------------+--------+
|       name       | abbrev | utc_offset | is_dst |
+------------------+--------+------------+--------+
| Asia/Tokyo       | JST    | PT9H       | f      |
| Asia/Ho_Chi_Minh | +07    | PT7H       | f      |
+------------------+--------+------------+--------+

select '2019-09-26'::timestamp
     , timestamp '2019-09-26 16:41:39';

+---------------------+---------------------+
|      timestamp      |      timestamp      |
+---------------------+---------------------+
| 2019-09-26 00:00:00 | 2019-09-26 16:41:39 |
+---------------------+---------------------+

select '2019-09-26 02:41:39 UTC'::timestamptz
     , timestamptz '2019-09-26 02:41:39 Zulu';

+------------------------+------------------------+
|      timestamptz       |      timestamptz       |
+------------------------+------------------------+
| 2019-09-26 10:41:39+08 | 2019-09-26 10:41:39+08 |
+------------------------+------------------------+

Zulu 是美軍常用的代號. 顯示時是以現在的 timezone 來表示.
因為 Taipei 時間,相對是 UTC +08 , 但是表示法後面的 +08 
有時候會讓人有一個還要再加8的感覺...這個要忍住衝動,不要心算再加上去.

不過別擔心,再搭配以下的例子.

select timestamptz '2019-09-26 10:41:39+08' at time zone 'Asia/Taipei' as "Taiei#1"
     , timestamptz '2019-09-26 02:41:39 Zulu' at time zone 'Asia/Taipei' as "Taiei#2"
     , timestamptz '2019-09-26 10:41:39+08' at time zone 'Asia/Tokyo' as "Tokyo";
+---------------------+---------------------+---------------------+
|       Taiei#1       |       Taiei#2       |        Tokyo        |
+---------------------+---------------------+---------------------+
| 2019-09-26 10:41:39 | 2019-09-26 10:41:39 | 2019-09-26 11:41:39 |
+---------------------+---------------------+---------------------+

注意這次是用 timestamp 

select timestamp '2019-09-26 10:41:39' at time zone 'Asia/Taipei' as "Taiei"
     , timestamp '2019-09-26 11:41:39' at time zone 'Asia/Tokyo' as "Tokyo";

+------------------------+------------------------+
|         Taiei          |         Tokyo          |
+------------------------+------------------------+
| 2019-09-26 10:41:39+08 | 2019-09-26 10:41:39+08 |
+------------------------+------------------------+

通常的說法,東京比台北快一小時.也就是說當東京11點時,台北是10點.
有沒有注意到,這個 at time zone 子句, 會將有時區的轉換成無時區的當地時間,反之亦然.

select timestamp '2019-09-26 10:41:39' at time zone 'Asia/Taipei' at time zone 'Asia/Taipei' as "Taiei Local Again";
+---------------------+
|  Taiei Local Again  |
+---------------------+
| 2019-09-26 10:41:39 |
+---------------------+

昨天在討論 current_timestamp, clock_timestamp() 時,有注意到 current_timestamp 是以 transaction 時刻.

current 開頭的日期時間函數,還有 local 開頭的日期時間函數,都是 start time of the current transaction.
這些都是 SQL 標準的.
Postgresql 另外有 now(), 這是等於 transaction_timestamp() 也等於 CURRENT_TIMESTAMP,方便使用時可以少打字.
另外有statement_timestamp(),顧名思義,就是每道語句的.當然還有 clock_timestamp().

接著我們來看 transaction 裡面,變更 timezone, 然後輸入時間戳,會是怎樣的情況.

create table ithelp190926 (
  purets timestamp
, tstz timestamp with time zone
);

commit;

begin;

insert into ithelp190926 (purets, tstz)
select statement_timestamp(), statement_timestamp();

insert into ithelp190926 (purets, tstz)
select now(), now();

set timezone to 'Asia/Tokyo';

insert into ithelp190926 (purets, tstz)
select now(), now();

set timezone to 'Asia/Ho_Chi_Minh';

insert into ithelp190926 (purets, tstz)
select now(), now();

set timezone to 'Asia/Taipei';

commit;

-- transaction end

來看一下輸入的資料

select * from ithelp190926;

+----------------------------+-------------------------------+
|           purets           |             tstz              |
+----------------------------+-------------------------------+
| 2019-09-26 21:22:49.068371 | 2019-09-26 21:22:49.068371+08 |
| 2019-09-26 21:21:59.462832 | 2019-09-26 21:21:59.462832+08 |
| 2019-09-26 22:21:59.462832 | 2019-09-26 21:21:59.462832+08 |
| 2019-09-26 20:21:59.462832 | 2019-09-26 21:21:59.462832+08 |
+----------------------------+-------------------------------+
(4 rows)

注意到 statement_timestamp() 的時刻,比較晚. now() 就是 transaction 開始的時刻.
有 with time zone 不管時區怎樣改變,實際上的時刻都是一樣的,
表示法是以最後設定回來的台北來表示.

現在把時區調成東京.

set timezone to 'Asia/Tokyo';

select * from ithelp190926;

+----------------------------+-------------------------------+
|           purets           |             tstz              |
+----------------------------+-------------------------------+
| 2019-09-26 21:22:49.068371 | 2019-09-26 22:22:49.068371+09 |
| 2019-09-26 21:21:59.462832 | 2019-09-26 22:21:59.462832+09 |
| 2019-09-26 22:21:59.462832 | 2019-09-26 22:21:59.462832+09 |
| 2019-09-26 20:21:59.462832 | 2019-09-26 22:21:59.462832+09 |
+----------------------------+-------------------------------+

可以觀察到, 有時區的很方便.
還是要記得調回台北喔

set timezone to 'Asia/Taipei';

根據上面的研討,兩種所佔用的空間是一樣的,都是 8 bytes.
有時區的會自動調整,使用上很方便.
有些人主張,全部存成 UTC 的,然後再自行計算調整.這也是一種方法.
若以使用 Postgresql 為主的情況下,使用有時區的格式,佔用空間相同,
而且帶來許多便利,是不錯的選擇.