Sqlite时差

Question

如何使用Python模块获得具有<2小时差的datetime列的行？

我试过这个：

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对象，这是可行的。
• 这可以用来测试两次日期是否是同一天，这要感谢DATE函数： 从mytable mt1中选择mt1.*，mt2.*，mytable mt2其中日期(mt1.date)= DATE(mt2.date)

Answer 1

这要归功于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时间戳)。