Skip to main content
 首页 » 编程设计

python之如何使用 python 在 sqlite3 中存储时区感知时间戳

2025年04月02日16emanlee

在混合原始日期时间和存储在 sqlite3 上的感知日期时间时遇到一些麻烦。

>>> import datetime, pytz 
>>> dt = datetime.datetime.now() 
>>> dt1 = pytz.utc.localize(dt) 
>>> dt < dt1 
Traceback (most recent call last): 
  File "<stdin>", line 1, in <module> 
TypeError: can't compare offset-naive and offset-aware datetimes 

然后在 SQLite3 中,tz 被去掉了:

>>> conn = sqlite3.connect(':memory:', detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES) 
>>> c = conn.cursor() 
>>> c.execute("CREATE TABLE example (id INTEGER PRIMARY KEY, title text, updated timestamp)") 
<sqlite3.Cursor object at 0x1058855e0> 
>>> conn.commit() 
>>> c.execute("INSERT INTO example VALUES (NULL, ?, ?)", ('naive', datetime.datetime.utcnow())) 
<sqlite3.Cursor object at 0x1058855e0> 
>>> conn.commit() 
>>> c.execute("INSERT INTO example VALUES (NULL, ?, ?)", ('tz-aware', pytz.utc.localize(datetime.datetime.utcnow()))) 
<sqlite3.Cursor object at 0x1058855e0> 
>>> conn.commit() 
>>> c.execute("SELECT * FROM example") 
<sqlite3.Cursor object at 0x1058855e0> 
>>> c.fetchall() 
[(1, u'naive', datetime.datetime(2016, 4, 19, 22, 26, 57, 337504)), (2, u'tz-aware', datetime.datetime(2016, 4, 19, 22, 27, 41, 664158))] 
>>> 

请您参考如下方法:

找到答案 - 虽然使用箭头。

>>> import arrow 
>>> def convert_arrowdatetime(s): 
...     return arrow.get(s) 
... 
>>> def adapt_arrowdatetime(adt): 
...     return adt.isoformat() 
... 
>>> sqlite3.register_adapter(arrow.arrow.Arrow, adapt_arrowdatetime) 
>>> sqlite3.register_converter("timestamp", convert_arrowdatetime) 
>>> c.execute("INSERT INTO example VALUES (NULL, ?, ?)", ('tz-aware', pytz.utc.localize(datetime.datetime.utcnow()))) 
<sqlite3.Cursor object at 0x1058855e0> 
>>> conn.commit() 
>>> c.execute("SELECT * FROM example") 
<sqlite3.Cursor object at 0x1058855e0> 
>>> c.fetchall() 
[(1, u'naive', <Arrow [2016-04-19T22:26:57.337504+00:00]>), (2, u'tz-aware', <Arrow [2016-04-19T22:27:41.664158+00:00]>), (3, u'tz-aware', <Arrow [2016-04-19T22:35:12.004054+00:00]>)] 
>>>