SQLite datetime时差

问题描述

如何获取具有datetime列且行<的行.使用Python Sqlite sqlite3 模块有2个小时的时间差吗?

How to get the rows that have a datetime column with < 2 hours difference, with Python Sqlite sqlite3 module?

我尝试过:

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 差异计算不起作用:

but the datetime difference computation doesn't work:

c.execute('SELECT ABS(mt1.date - mt2.date) FROM mytable mt1, mytable mt2')

因此最终不可能在 WHERE ABS(mt1.date-mt2.date)<中使用查询2 按2小时最大日期时间差进行过滤.

so it's finally impossible to use a query with WHERE ABS(mt1.date - mt2.date) < 2 to filter by 2-hours max datetime difference.

如何执行此操作?

注意:

• detect_types = sqlite3.PARSE_DECLTYPES 确保查询返回 datetype Python对象，并且该对象正常运行.

• detect_types=sqlite3.PARSE_DECLTYPES ensures that the query returns a datetype Python object, and this is working.

这可以测试两个日期时间是否在同一天，这要感谢 DATE 函数:

this works to test if the 2 datetimes are the same day, thanks to DATE function:

SELECT mt1.*, mt2.* FROM mytable mt1, mytable mt2 WHERE DATE(mt1.date) = DATE(mt2.date)

推荐答案

JULIANDAY 允许将日期时间差计算为浮点数:

This works thanks to JULIANDAY which allows to compute a datetime difference as a float:

SELECT ABS(JULIANDAY(mt1.date) - JULIANDAY(mt2.date)) FROM mytable mt1, mytable mt2

因此2个小时的差异可以转化为这种情况:

So a 2-hours-difference can be translated into this condition:

ABS(JULIANDAY(mt1.date) - JULIANDAY(mt2.date)) < 0.083333

因为2hrs/24hrs = 1/12〜0.083333

since 2hrs/24hrs = 1/12 ~ 0.083333

此查询也可以正常工作:

This query works as well:

SELECT ABS(STRFTIME("%s", mt1.date) - STRFTIME("%s", mt2.date)) FROM mytable mt1, mytable mt2

，两个小时的条件是:

ABS(STRFTIME("%s", mt1.date) - STRFTIME("%s", mt2.date)) < 7200

即最大差异为7200秒( STRFTIME(％s"，mt1.date)提供Unix时间戳).

i.e. a 7200 seconds max difference (STRFTIME("%s", mt1.date) gives the Unix timestamp).

这篇关于SQLite datetime时差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！