如何用Python的sqlite3模块筛选datetime列差值小于2小时的行？

嘿，我来帮你搞定这个需求！你已经搭好了内存数据库和测试数据，接下来只需要调整查询逻辑就能轻松筛选出符合条件的行啦。

核心思路 #

SQLite自带了好用的时间处理函数，我们可以把datetime转换成可计算的数值，再判断差值是否小于2小时。常用的两种方式：

• 用JULIANDAY函数转成儒略日，2小时对应的儒略日差值是2/24（因为一天24小时）
• 用strftime('%s', date)转成Unix时间戳（秒），2小时等于7200秒，判断差值是否小于这个数

完整代码示例 #

下面是包含创建表、插入数据和查询的完整代码：

import sqlite3, datetime

# 连接内存数据库，开启类型解析让SQLite自动识别datetime类型
db = sqlite3.connect(':memory:', detect_types=sqlite3.PARSE_DECLTYPES)
c = db.cursor()

# 创建测试表
c.execute('CREATE TABLE mytable (id integer, date timestamp)')

# 插入测试数据：前两条差1小时，第三条和第一条差4小时
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,2,3,0),))

# 方式1：和指定目标时间比较，找出差小于2小时的行
target_time = datetime.datetime(2018,1,1,23,0)
c.execute('''
    SELECT * FROM mytable 
    WHERE ABS(JULIANDAY(date) - JULIANDAY(?)) < 2/24
''', (target_time,))

print("与目标时间差小于2小时的行：")
for row in c.fetchall():
    print(row)

# 方式2：找出所有两两之间时间差小于2小时的行（自连接查询）
print("\n两两时间差小于2小时的行对：")
c.execute('''
    SELECT t1.id, t1.date, t2.id, t2.date
    FROM mytable t1
    JOIN mytable t2 ON t1.id < t2.id  # 避免重复配对
    WHERE ABS(strftime('%s', t1.date) - strftime('%s', t2.date)) < 7200
''')
for pair in c.fetchall():
    print(pair)

# 别忘了关闭连接
db.close()

代码说明 #

• detect_types=sqlite3.PARSE_DECLTYPES让SQLite自动把timestamp类型的列转换成Python的datetime对象，不用手动处理字符串转时间
• 自连接查询里用t1.id < t2.id是为了避免出现（1,2）和（2,1）这样的重复结果，如果你需要所有配对可以去掉这个条件
• 两种时间计算方式都能实现需求，选你觉得直观的就行

内容的提问来源于stack exchange，提问作者Basj