Datetime in SQLite and Python
要正確處理時間並不容易。承接我們先前的例子，其實是直接把時間轉換出來的字串存在 SQLite 裡。這有幾個問題。
首先是時區的問題。我們直接把 server 所在時區的時間存到資料庫去，台北的時區為 Asia/Taipei (UTC+8)。如果今天 server 跑到另一個時區，例如東京 Asia/Tokyo (UTC+9) 好了，這時候資料庫裡就包含了兩個時區的時間，但因為是字串是完全看不出差異的。
再來用字串存時間也有一些問題。首先是排序，雖然我們的例子是能正確的排序，但如果時間格式換了（像 %H:%M:%S %Y-%m-%d）那就不一定。再來可以看到後續想處理時間就會比較複雜。不過這一部份是因為 SQLite 沒有專門處理日期時間的資料型態，像 PostgreSQL 就能看得懂。
所以想要正確處理時間有幾個要點：
- 存到資料庫的時間應該要 UTC 來表示
- 在處理時間時（排序、顯示、處理時區），應該轉成正確的資料格式（例如 datetime）
- 呈現給使用者時再轉換到該人（或 server）所在時區
底下是比較正確處理時間的方式。
時區（Timezone）
我們都還沒有處理過時區。時區在 Python 內建的 datetime 只是個「概念」，也就是說，使用者可以傳進去不同的時區（存在 datetime.tzinfo 中），Python 能針對有提供時區的 datetime 做正確的判斷。但台北的時區是多少，紐約的時區是多少它不知道。
為什麼不處理各地時區這麼重要的概念？因為時間變動的速度很快，加上日光節約時間每年可能都不一樣，Python 下一版還沒出時區的資訊已經更新了很多次。
因此在 Python 中實際上時區處理靠得是第三方套件 pytz。像安裝 Flask 一樣，用 pip install pytz 就可以了。
實際操作看看。
>>> from datetime import datetime
>>> datetime.now() # local time
datetime.datetime(2015, 9, 29, 16, 33, 39, 537111)
>>> datetime.utcnow() # UTC time
datetime.datetime(2015, 9, 29, 8, 33, 39, 538745)
首先，可以看到 datetime 本身提供了 now() 以及 utcnow() 兩個 function 來拿到現在的時間。台北是 UTC+8 所以時間比 UTC 時間字面上快 8 小時。注意到兩個回傳的 datetime 物件都沒有包含時區的資訊。
處理時間原則上都以 UTC 為基準。我們建立一個 UTC 的現在時間存在變數 utcnow，並且用 pytz 處理時間。Import pytz 進來，並且定義了兩個時區：UTC 以及 TPE（台北時間）。
>>> import pytz
>>> utc = pytz.utc
>>> tpe = pytz.timezone('Asia/Taipei')
>>> utcnow = datetime.utcnow()
>>> utcnow
datetime.datetime(2015, 9, 29, 8, 38, 14, 738241)
>>> utc.fromutc(utcnow)
datetime.datetime(2015, 9, 29, 8, 38, 14, 738241, tzinfo=<UTC>)
>>> tpe.fromutc(utcnow)
datetime.datetime(2015, 9, 29, 16, 38, 14, 738241, tzinfo=<DstTzInfo 'Asia/Taipei' CST+8:00:00 STD>)
>>> utc.fromutc(utcnow) == tpe.fromutc(utcnow)
True
用 pytz 定義的時區處理 datetime 之後就會多了 tzinfo 的資訊。這時也能正確比較不同時區的時間。
如何處理一個任意定義的時間呢？例如 2016 年台北元旦好了，
>>> str(datetime(2016, 1, 1, 0, 0))
'2016-01-01 00:00:00'
>>> tpe_2016_newyear = tpe.localize(datetime(2016, 1, 1, 0, 0))
>>> tpe_2016_newyear
datetime.datetime(2016, 1, 1, 0, 0, tzinfo=<DstTzInfo 'Asia/Taipei' CST+8:00:00 STD>)
>>> utc.normalize(tpe_2016_newyear)
datetime.datetime(2015, 12, 31, 16, 0, tzinfo=<UTC>)
使用 .localize(<datetime>) 給予一個初始沒有時區資訊的 datetime 時區。有了時區之後，要在不同時區間轉換就使用 .normalize(<datetime>)。
可以再查查當台北 2016 元旦時，美國東岸時間是幾點。
>>> est = pytz.timezone('US/Eastern')
>>> est.normalize(tpe_2016_newyear)
datetime.datetime(2015, 12, 31, 11, 0, tzinfo=<DstTzInfo 'US/Eastern' EST-1 day, 19:00:00 STD>)
以後要看球賽轉播、重要發表就不會再搞不清楚時間了。
Datetime in SQLite, again
我們會處理 datetime 與時區了，那麼就來改寫一下本來 SQLite 存時間的方式。其實 Python datetime 支援 SQLite 轉換，同樣從Python module 說明文件裡面拿出來的。
>>> from datetime import datetime
>>> import sqlite3
>>> import pytz
>>> utc = pytz.utc
>>> tpe = pytz.timezone('Asia/Taipei')
>>> db = sqlite3.connect(
... "test.db",
... detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES
... )
>>> db.execute('CREATE TABLE test(dt timestamp)')
<sqlite3.Cursor object at 0x10a59b960>
資料欄位的設為 timestamp，並且在連接的時候設定 PARSE_DECLTYPES 及 PARSE_COLNAMES，稍後可以看到他們的效果。
趕快把時間存進去吧。
>>> utcnow = datetime.utcnow()
>>> utcnow
datetime.datetime(2015, 9, 29, 12, 48, 16, 671538)
>>> with db:
... db.execute(
... 'INSERT INTO test(dt) VALUES (?)',
... (utcnow, )
... )
...
<sqlite3.Cursor object at 0x1082380a0>
>>> tpe_2016_newyear = tpe.localize(datetime(2016, 1, 1, 0, 0))
>>> utc.normalize(tpe_2016_newyear)
datetime.datetime(2015, 12, 31, 16, 0, tzinfo=<UTC>)
>>> utc_dt = utc.normalize(tpe_2016_newyear).replace(tzinfo=None)
>>> with db:
... db.execute(
... 'INSERT INTO test(dt) VALUES (?)',
... (utc_dt, )
... )
...
<sqlite3.Cursor object at 0x10a59b960>
存了兩個時間，一個是 UTC 的現在時間，另一個是以 UTC 表示的台北 2016 元旦。注意兩個時間都把 UTC 時區去掉了，因為在某些情況底下 SQLite 與 python 的 datetime adapter 會看不懂時區（這是個 bug #19065）。
如果用 SQLite 可以看到時間都是以 UTC 呈現。仍可以用它內建的 datetime('<UTC time>', 'localtime') 把 UTC 時間字串轉換成電腦的當地時間。這樣處理是容易與其他應用程式相容的。
-- sqlite3 test.db
sqlite> .schema
CREATE TABLE test(dt timestamp);
sqlite> SELECT dt FROM test;
2015-09-29 12:48:16.671538
2015-12-31 16:00:00
sqlite> SELECT datetime(dt, 'localtime') FROM test;
2015-09-29 20:48:16
2016-01-01 00:00:00
再用 Python 讀回來仍然是 datetime 格式：
>>> ret_vals = db.execute('SELECT dt AS "[timestamp]" FROM test').fetchall()
>>> ret_vals
[(datetime.datetime(2015, 9, 29, 12, 48, 16, 671538),),
(datetime.datetime(2015, 12, 31, 16, 0),)]
>>> [tpe.fromutc(t[0]) for t in ret_vals]
[datetime.datetime(2015, 9, 29, 20, 48, 16, 671538, tzinfo=<DstTzInfo 'Asia/Taipei' CST+8:00:00 STD>),
datetime.datetime(2016, 1, 1, 0, 0, tzinfo=<DstTzInfo 'Asia/Taipei' CST+8:00:00 STD>)]
Python 3 內建 timezone 支援
為了寫這篇 blog 又研究了一下內建的 datetime.timezone。Python 2 沒有這個功能，不過基本的 timedelta 有，所以要自己做應該也是做得到…吧？
內建的 datetime.timezone 由一個 utcoffset 做建立，基本上就是傳個相對於 UTC 的時間差，以 datetime.timedelta 表示。一樣內建帶有 UTC 時區，這邊試著建了台北以及東京的時間。
>>> import datetime as dt
>>> tpe_now = dt.datetime.now()
>>> tpe_now
datetime.datetime(2015, 9, 29, 20, 40, 49, 347568)
>>> utc = dt.timezone.utc
>>> tpe_delta = dt.timedelta(hours=8)
>>> tpe = dt.timezone(tpe_delta)
>>> jpn_delta = dt.timedelta(hours=9)
>>> jpn = dt.timezone(jpn_delta)
我人在台北，所以 datetime.datetime.now() 會給我台北時間，再用 timedelta 手動算出各時區的時間。
>>> utc_now = tpe_now - tpe_delta # manually time shift
>>> jpn_now = utc_now + jpn_delta
>>> utc_now
datetime.datetime(2015, 9, 29, 12, 40, 49, 347568)
>>> tpe_now == utc_now
False
直接比較這些算出來的時間，不意外不相等，因為預設的 tzinfo 是空的。
>>> tpe_now.replace(tzinfo=tpe)
datetime.datetime(2015, 9, 29, 20, 40, 49, 347568, tzinfo=datetime.timezone(datetime.timedelta(0, 28800)))
>>> tpe_now.replace(tzinfo=tpe) == utc_now.replace(tzinfo=utc)
True
>>> jpn_now.replace(tzinfo=jpn) == tpe_now.replace(tzinfo=tpe)
True
給了各地的時區的 tzinfo 之後，可以看到 datetime 在做比較的時候是有考慮時區位移的。
接著再來看一下pytz 與內建 datetime.timezone 的相容程度。
>>> import pytz
>>> pytz_utc = pytz.utc
>>> pytz_tpe = pytz.timezone('Asia/Taipei')
>>> pytz.utc.normalize(jpn_now.replace(tzinfo=jpn))
datetime.datetime(2015, 9, 29, 12, 40, 49, 347568, tzinfo=<UTC>)
>>> pytz_tpe.localize(tpe_now) == utc_now.replace(tzinfo=utc)
True
比較跟轉換都沒有問題，可以放心轉換。
讓 Python 內建 SQLite adapter 支援時區
看了一下 Python issue 19065，之所以沒有解決其實是缺 patch，因為現在的 patch 並不相容 Python 2.x（沒有 datetime.timezone），然後 pysqlite 的維護者並沒有想要支援 timezone 的意思。
不過那只是內建的 adapter for datetime.datetime object，要自己做也沒問題。參考 issue 裡面提供的解法（在 Github gist 上）。
# tz_aware_adpater.py
# Adapt from https://gist.github.com/acdha/6655391
import datetime
import sqlite3
def tz_aware_timestamp_adapter(val):
datepart, timepart = val.split(b" ")
year, month, day = map(int, datepart.split(b"-"))
if b"+" in timepart:
timepart, tz_offset = timepart.rsplit(b"+", 1)
if tz_offset == b'00:00':
tzinfo = datetime.timezone.utc
else:
hours, minutes = map(int, tz_offset.split(b':', 1))
tzinfo = datetime.timezone(
datetime.timedelta(hours=hours, minutes=minutes))
else:
tzinfo = None
timepart_full = timepart.split(b".")
hours, minutes, seconds = map(int, timepart_full[0].split(b":"))
if len(timepart_full) == 2:
microseconds = int('{:0<6.6}'.format(timepart_full[1].decode()))
else:
microseconds = 0
val = datetime.datetime(
year, month, day, hours, minutes, seconds, microseconds,
tzinfo
)
return val
sqlite3.register_converter('timestamp', tz_aware_timestamp_adapter)
python3 -i tz_aware_adpater.py
>>> db = sqlite3.connect(
... 'test.db',
... detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES
... )
>>> import pytz
>>> tpe = pytz.timezone('Asia/Taipei')
>>> pycontw = tpe.localize(datetime.datetime(2016, 6, 3, 8, 0))
>>> str(pycontw)
'2016-06-03 08:00:00+08:00'
>>> db.executemany(
... 'INSERT INTO test(dt) VALUES (?)',
... [(pycontw,), (pytz.utc.normalize(pycontw),)]
... )
>>> db.commit()
存了兩個帶有時區的時間（兩個時間是相同的）。先從 SQLite 來讀讀看。
sqlite> SELECT dt FROM test;
2015-09-29 12:48:16.671538
2015-12-31 16:00:00
2016-06-03 08:00:00+08:00
2016-06-03 00:00:00+00:00
sqlite> SELECT datetime(dt, 'localtime') FROM test;
2015-09-29 20:48:16
2016-01-01 00:00:00
2016-06-03 08:00:00
2016-06-03 08:00:00
時區是直接寫到 SQLite 裡面，沒有的話就當成是 UTC 時區。
再用 Python 讀回來，測一下修改的 adapter。
>>> dts = db.execute('SELECT dt FROM test').fetchall()
>>> dts
[(datetime.datetime(2015, 9, 29, 12, 48, 16, 671538),),
(datetime.datetime(2015, 12, 31, 16, 0),),
(datetime.datetime(2016, 6, 3, 8, 0, tzinfo=datetime.timezone(datetime.timedelta(0, 28800))),),
(datetime.datetime(2016, 6, 3, 0, 0, tzinfo=datetime.timezone.utc),)]
>>> [t[0].astimezone(tpe) for t in dts[2:]]
[datetime.datetime(2016, 6, 3, 8, 0, tzinfo=<DstTzInfo 'Asia/Taipei' CST+8:00:00 STD>),
datetime.datetime(2016, 6, 3, 8, 0, tzinfo=<DstTzInfo 'Asia/Taipei' CST+8:00:00 STD>)]
讀回來沒有問題，如果要完整處理所有情況（前面兩個 datetime 是 naive 沒有時區）
>>> [(t[0].astimezone(utc) if t[0].tzinfo is not None
... else utc.localize(t[0]))
... for t in dts]
[datetime.datetime(2015, 9, 29, 12, 48, 16, 671538, tzinfo=<UTC>),
datetime.datetime(2015, 12, 31, 16, 0, tzinfo=<UTC>),
datetime.datetime(2016, 6, 3, 0, 0, tzinfo=<UTC>),
datetime.datetime(2016, 6, 3, 0, 0, tzinfo=<UTC>)]
>>> [(t[0].astimezone(tpe) if t[0].tzinfo is not None
... else tpe.fromutc(t[0]))
... for t in dts]
[datetime.datetime(2015, 9, 29, 20, 48, 16, 671538, tzinfo=<DstTzInfo 'Asia/Taipei' CST+8:00:00 STD>),
datetime.datetime(2016, 1, 1, 0, 0, tzinfo=<DstTzInfo 'Asia/Taipei' CST+8:00:00 STD>),
datetime.datetime(2016, 6, 3, 8, 0, tzinfo=<DstTzInfo 'Asia/Taipei' CST+8:00:00 STD>),
datetime.datetime(2016, 6, 3, 8, 0, tzinfo=<DstTzInfo 'Asia/Taipei' CST+8:00:00 STD>)]
總結
時區真的很煩，尤其是很多地方不一定都完整支援時區，最好的情況還是用 UTC 溝通，只有在真的需要時再轉換成當地時間。
如果大家對時區很有興趣，不久前 PEP 495 已經被接受，沒有意外應該會出現在 Python 3.6 裡面，它處理的是日光節約時間的問題。（感覺在臺灣對日光節約時間完全沒有概念啊）
不得不說要正確處理時間…很麻煩啊。