以目前我們的系統來看, 與我們業務相關的系統, 都使用 PST 時區, 只有我們員工的打卡系統存放的是當地時間的時間. 所以我們用 php 開發系統時, 也做了一個在 PST 與 GMT+8 這兩個時區之間轉換的函式, 一般來說, 除了 PST 有夏令節約時間的問題外, 並沒有太大的問題存在.

不過, 隨著客戶的生意愈來愈大, 目前幾乎全球都有銷售他們的產品, 所以幫客戶做技術支援工作的敝公司, 原本服務的對象就由北美地區, 逐漸擴大到全球各地的英語系國家. 而這些其他地區的報表, 我們依然使用 PST 時間來計算. 但是, 這幾天, 我們客戶的英國分公司, 要求我們改用 GMT 來製作他們那邊的報表.... 這難道也要把資料抓下來後, 用 php 來轉換嗎? 這可不像我們之前系統的需求, 所有抓出來的資料都是要顯示的, 只是再用一個函式轉換不同的時區罷了. 這些報表, 如果改用別的時區在選擇資料, 要用 php 做的話, 那可不是一件小事, 因為有很多 group by 的動作, 在資料庫上頭可能跑個一分鐘就可以抓到資料, 如果是自己把一筆一筆的資料由資料庫抓出來, 再用 php 慢慢一筆一筆運算, 依據以往的經驗, 原本一分鐘的事, 可能要跑上半個小時以上才能跑完, 不管是考慮我們系統的負荷, 或使用者等待的時間, 應該都是不能夠接受的.

所以, 上網查看看我們使用的 PostgreSQL 資料庫, 對於這種不同時區的時間轉換, 是否有特別的方法可以處理. 果然在文件中有提到可以利用 timezone() 來轉換 timestamp 到不同的時區的時間. 不過, 我們之前為了方便使用, 並不是將時間存成 timestamp 格式, 而是使用 8 個字元的字串存放日期, 再用 6 個字元的字串存放時間. 現在要轉換時區, 這樣的存放方式, 反而變的很麻煩. 還好, 有看到 to_timestamp() 這個函式可以使用:

select to_timestamp('20060228' || '123000' || ' PST',
                     'FXYYYYMMDDHH24MISS TZ'); 

執行結果:

ERROR:  "TZ"/"tz" not supported 

不過, 上頭這個指令, 卻得到了一個錯誤訊息.  試著把 TZ 移除:

select to_timestamp('20060228' || '123000',
                     'FXYYYYMMDDHH24MISS'); 

執行結果:

2006-02-28 12:30:00 +08 

果然, 移除 TZ 之後, 就可以得到我們當地時區的時間. 不過我們的資料應該是 PST 時區的時間才對. 一定要加上 TZ 才可以. 查了很久, 終於在 PostgreSQL 的 source code 中看到有關這個 TZ 的運作, 只有在 TO_CHAR 上頭有作用, 如果是在 FROM_CHAR 時, 就會得到上頭那個錯誤. 看來, 似乎要放棄使用 to_timestamp(), 但是, 似乎又沒看到其他更好的方法. 後來我們試著用 cast 來轉換格式:

select cast(
         to_timestamp('20060228' || '123000',
                      'FXYYYYMMDDHH24MISS') as timestamp
            ); 

執行結果:

2006-02-28 12:30:00 

把 to_timestamp() 轉換出來的 timestamptz 格式, 強迫轉換成 timestamp 格式, 我們果然得到了一個沒有時區的時間了. 但是我們要的應該是 PST 時區才對, 所以試著再轉換一次:

select cast(
         cast(
          to_timestamp('20060228' || '123000',
                       'FXYYYYMMDDHH24MISS') as timestamp
             ) || ' PST' as timestamptz
            ); 

執行結果:

2006-03-01 04:30:00 +08 

我們總算得到了一個正確帶時區的 timestamp, 2006-03-01 04:30:00 +08 就等於 2006-02-28 12:30:00 PST 沒錯. 得到正確的 timestamp 之後, 接下來的動作就變的很簡單了, 我們只要用 timezone() 這個函式就可以切換到不同的時區:

select timezone('GMT',
           cast(
            cast(
             to_timestamp('20060228' || '123000',
                          'FXYYYYMMDDHH24MISS') as timestamp
                ) || ' PST' as timestamptz
               )
                ); 

執行結果:

2006-02-28 20:30:00 

把這個 timestamp 換成 GMT 時區看看, 果然得到正確的 2006-02-28 20:30:00 這個時間. 

最後, 我們就在系統的 form 上頭, 加上一個時區的選項, 把 PostgreSQL 所支援的所有時區列上, 讓使用者選擇所要用的時區, 然後在我們使用的 SQL 指令上頭, 改成這樣:

select to_number(
         to_char(
          timezone('$timezone',
           cast(
            cast(
             to_timestamp(a.row_date ||
                          to_char(a.starttime, 'FM0000') ||
                          '00',
                          'FXYYYYMMDDHH24MISS') as timestamp
                ) || ' PST' as timestamptz
               )
                ), 'HH24MI'), '9990') as starttime,
        sum(a.acdcalls) as acdcalls,
        sum(a.abncalls) as abncalls,
        sum(a.acdtime) as acdtime,
        sum(a.abntime) as abntime,
        sum(a.anstime) as anstime,
        sum(a.in_sl) as in_sl
from bcms_hsplit as a join cms_split as b on a.split = b.split and a.acd = b.acd
where a.row_date >= '$s_date_before'
and a.row_date <= '$e_date_after'
and to_char(
      timezone('$timezone',
       cast(
        cast(
         to_timestamp(a.row_date ||
                      to_char(a.starttime, 'FM0000') ||
                      '00',
                      'FXYYYYMMDDHH24MISS') as timestamp
            ) || ' PST' as timestamptz
           )
              ), 'YYYYMMDD') between '$s_date_str' and '$e_date_str'
group by 1
order by 1 

利用選取的 $timezone 變數來轉換時區, 把系統用的 row_date 與 starttime 轉換成 timestamp 再處理, 就可以得到正確的資料. 要注意的是, 如果只用後頭那個轉換時區的運算式來判斷選取的資料, 會造成 full table scan, 當資料很多時, 這個動作可能會做很久, 會造成很大的 performance 問題. 所以, 我們先行取得 $s_date_str 的前一天, 與 $e_date_str 的後一天, 利用這個資料來設定 row_date 的範圍 (因為 timezone 的轉換可能會往前或往後一天), 這樣子就不致於造成 full table scan, 可以大幅縮短執行時間. (不過比起不用轉換的 SQL 指令來說, 還是慢上許多, 但是, 至少, 我們現在可以選擇我們要使用的時區來找資料了)