RoR中時區處理的問題

問題一開始是出現在一段很簡單也很常需要使用的程式碼, 程式希望撈出該年月所含括的資料. 資料庫裡面存的時間時區是UTC.

我們原先從vendor得到的程式碼如下, 但是運作一直不是很正常, 撈出來的資料總是有問題, 但vendor卻始終認為該程式碼沒有錯誤.

date1 = Date.new(year, month, 1)
date2 = Date.new(year, month, -1)
self.find(:all, :conditions => ["start_at >= ? and start_at <= ?", date1.beginning_of_day, date2.end_of_day] )

要求fix bug未果, 不得以只好自己動手, 台灣的時區是UTC+8, 因此當我們想要撈出所有台灣時區2009/6的資料時, 所需要下的SQL stmt其實是 2009-06-01 00:00:00+0800與2009-06-30 23:59:59+0800等同於2009-05-31 16:00:00+0000與2009-06-30 15:59:59+0000不幸的是, 上面這段程式碼並無法正常的運作.

>> puts Time.zone
>> puts date1.beginning_of_day.zone
>> puts date1.beginning_of_day
>> puts date1.beginning_of_day.to_s(:db)
>> puts date1.beginning_of_day.in_time_zone(Time.zone).zone
>> puts date1.beginning_of_day.in_time_zone(Time.zone)
>> puts date1.beginning_of_day.in_time_zone(Time.zone).to_s(:db)
>> puts Date.parse("2009-6-01T00:00:00+00:00").beginning_of_day.in_time_zone(Time.zone).strftime("%Y-%m-%d %H:%M:%S")

=> (GMT+08:00) Taipei
=> CST
=> 2009/06/01 12:00 AM
=> 2009-06-01 00:00:00
=> CST
=> 2009/06/01 12:00 AM
=> 2009-05-31 16:00:00
=> 2009-06-01 00:00:00

=> (GMT+10:00) Hobart
=> CST
=> 2009/06/01 12:00 AM
=> 2009-06-01 00:00:00
=> EST
=> 2009/06/01 2:00 AM
=> 2009-05-31 16:00:00
=> 2009-06-01 02:00:00

=> (GMT+00:00) UTC
=> CST
=> 2009/06/01 12:00 AM
=> 2009-06-01 00:00:00
=> UTC
=> 2009/05/31 4:00 PM
=> 2009-05-31 16:00:00
=> 2009-05-31 16:00:00

=> (GMT-10:00) Hawaii
=> CST
=> 2009/06/01 12:00 AM
=> 2009-06-01 00:00:00
=> HST
=> 2009/05/31 6:00 AM
=> 2009-05-31 16:00:00
=> 2009-05-31 06:00:00

剛看到Taipei部分的結果, 還以為使用date1.beginning_of_day.in_time_zone(Time.zone).to_s(:db)這段是可以正常運作的, 但是多測了幾個時區之後, 發現還是有問題, 無論設定任何時區, 永遠都得到2009-05-31 16:00:00這樣的值

而唯一會變動的兩個值是date1.beginning_of_day.in_time_zone(Time.zone)和Date.parse("2009-6-01T00:00:00+00:00").beginning_of_day.in_time_zone(Time.zone).strftime("%Y-%m-%d %H:%M:%S"), 但很奇怪的是, 這兩個數值變動的基礎都是先減8小時之後再計算與台灣地區的時差, 即使第二段程式是直接提供一個"2009-6-01T00:00:00+00:00"的時間, 希望能得到一個UTC時間之後再作計算, 但結果依然是失敗的.

後來在網路上好一陣搜尋之後, 才赫然發現這是個bug (or issue?)
https://rails.lighthouseapp.com/projects/8994/tickets/2479-inconsistant-time-zone-handling

文中的例子如下, 可以看出Date有時區處理的問題:

>> Date.today.beginning_of_day
=> Fri Apr 10 00:00:00 -0500 2009
>> Time.zone.now.beginning_of_day
=> Fri, 10 Apr 2009 00:00:00 CDT -05:00
>> Date.today.beginning_of_day == Time.zone.now.beginning_of_day
=> true

>> Person.all :conditions => [ "created_at > ?", Date.today.beginning_of_day ]
=> Generated SQL => SELECT * FROM "people" WHERE (created_at > '2009-04-10 00:00:00')

>> Person.all :conditions => [ "created_at > ?", Time.zone.now.beginning_of_day ]
=> Generated SQL => SELECT * FROM "people" WHERE (created_at > '2009-04-10 05:00:00')

簡單說就是要用Time來取得的時間才會是你要的, 因此將原先的程式修改為下面這樣就搞定啦!

fmTime = Time.zone.local(year, month, 1, 0, 0, 0)
toTime = Time.zone.local(year, month, -1, 23, 59 ,59)
self.find(:all, :conditions => ["start_at >= ? and start_at <= ?", fmTime, toTime] )

>> puts Time.zone
>> puts fmTime.to_s(:db)
>> puts toTime.to_s(:db)

=> (GMT+08:00) Taipei
=> 2009-05-31 16:00:00
=> 2009-06-30 15:59:59

=> (GMT+00:00) UTC
=> 2009-06-01 00:00:00
=> 2009-06-30 23:59:59

=> (GMT-10:00) Hawaii
=> 2009-06-01 10:00:00
=> 2009-07-01 09:59:59

如此終於搞定一個邏輯上很簡單處理的bug... RoR還是讓我很無言...