如何使用Python Sqlitesqlite3
模块获取日期时间列相差<2小时的行?
我试过这个:
import sqlite3, datetime
db = sqlite3.connect(':memory:', detect_types=sqlite3.PARSE_DECLTYPES)
c = db.cursor()
c.execute('CREATE TABLE mytable (id integer, date timestamp)')
c.execute('INSERT INTO mytable VALUES (1, ?)', (datetime.datetime(2018,1,1,23,0),))
c.execute('INSERT INTO mytable VALUES (2, ?)', (datetime.datetime(2018,1,2,0,0),))
c.execute('INSERT INTO mytable VALUES (3, ?)', (datetime.datetime(2018,1,9,0,0),))
此查询适用于:
c.execute('SELECT mt1.date, mt2.date FROM mytable mt1, mytable mt2')
并返回:
(datetime.datetime(2018, 1, 1, 23, 0), datetime.datetime(2018, 1, 1, 23, 0))(datetime.datetime(2018, 1, 1, 23, 0), datetime.datetime(2018, 1, 2, 0, 0))(datetime.datetime(2018, 1, 1,
23, 0
), datetime.datetime(2018, 1, 9, 0, 0))
...
(datetime.datetime(2018, 1, 9, 0, 0), datetime.datetime(2018, 1, 9, 0, 0))
但是datetime
差计算不起作用:
c.execute('SELECT ABS(mt1.date - mt2.date) FROM mytable mt1, mytable mt2')
(0,)(0,)
...
因此,最终不可能使用带有WHERE ABS(mt1.date - mt2.date) < 2
的查询来按 2 小时最大日期时间差异进行筛选。
怎么做?
注意:
detect_types=sqlite3.PARSE_DECLTYPES
确保查询返回datetype
Python 对象,并且这正在工作。
这适用于测试2个日期时间是否是同一天,这要归功于
DATE
函数:SELECT mt1.*, mt2.* FROM mytable mt1, mytable mt2 WHERE DATE(mt1.date) = DATE(mt2.date)
这要归功于JULIANDAY
它允许将日期时间差异计算为浮点数:
SELECT ABS(JULIANDAY(mt1.date) - JULIANDAY(mt2.date)) FROM mytable mt1, mytable mt2
因此,2 小时的差异可以转换为此条件:
ABS(JULIANDAY(mt1.date) - JULIANDAY(mt2.date)) < 0.083333
由于 2 小时/24 小时 = 1/12 ~ 0.083333
此查询也有效:
SELECT ABS(STRFTIME("%s", mt1.date) - STRFTIME("%s", mt2.date)) FROM mytable mt1, mytable mt2
2 小时条件为:
ABS(STRFTIME("%s", mt1.date) - STRFTIME("%s", mt2.date)) < 7200
即 7200 秒的最大差值(STRFTIME("%s", mt1.date)
给出 Unix 时间戳)。