[PostgreSQL] Get time zone offset from "timestamp with time zone" field.

這陣子遇到一個狀況，需要把PostgreSQL當中的timestamptz 欄位(timestamp with time zone)欄位轉成

2015-08-10 08:00:00+08:00

這樣的字串，
意思是當地時間早上八點，比UTC時間快8小時。

通常在psql console裡問時差，psql console會很清楚地寫出來

DB=# select now()::timestamp at time zone 'UTC';

           timezone            

-------------------------------

 2015-08-11 23:59:22.873966+08

(1 row)

但是PostgreSQL原生的

to_char(timestamptz,'YYYY-MM-DD HH24:MI:SSTZ')

只會得到

DB=# select to_char('2015-08-10 08:00:00+08:00'::timestamptz, 'YYYY-MM-DD HH24:MI:SSTZ');
        to_char         
------------------------
 2015-08-10 08:00:00CST
(1 row)

最後的TZ只會得到"CST"而不是"+08:00"

為了得到這個 "+08:00"
試了很多方法，譬如

DB=# SELECT current_setting('TIMEZONE');
 current_setting 
-----------------
 Asia/Taipei
(1 row)

DB=# select to_char(utc_offset, 'HH24:MI') from pg_timezone_names  where name =(SELECT current_setting('TIMEZONE'));
 to_char 
---------
 08:00
(1 row)

少了sign(正負號)

如果找比UTC慢的時區呢？

DB=# SELECT current_setting('TIMEZONE');
 current_setting
------------------
 America/St_Johns(1 row)

DB=# select to_char(utc_offset, 'HH24:MI') from pg_timezone_names  where name =(SELECT current_setting('TIMEZONE'));
 to_char
---------
 -2:-30
(1 row)

總之就是沒辦法吐出馬上可以用的字串

後來多google幾次，多翻幾次PostgreSQL文件，
拼湊出這個方法

執行的結果，用那個奇怪的 "慢兩個半小時" 時區來試試看

DB=# select '2015-02-20 02:00:00' at time zone 'UTC';
      timezone    
---------------------
 2015-02-20 05:30:00
(1 row)

DB=# select '2015-07-20 02:00:00' at time zone 'UTC';
      timezone    
---------------------
 2015-07-20 04:30:00
(1 row)

DB=# select get_tz_offset('2015-02-20 02:00:00' at time zone 'UTC');
 get_tz_offset
---------------
 -03:30
(1 row)

DB=# select get_tz_offset('2015-07-20 02:00:00' at time zone 'UTC');
 get_tz_offset
---------------
 -02:30
(1 row)

有時候時區設定沒改，
但2月份的時差跟7月份就是不一樣。

這就是Day light saving (日光節約時間)麻煩的地方啊....

最終要得到某一個timestamptz相對於UTC的時差，
還是問DB最準。