Sqlite Datetime Hour Difference

How to get the rows that have a datetime column with < 2 hours difference, with Python Sqlite sqlite3 module? I tried this: import sqlite3, datetime db = sqlite3.connect(':memor

Solution 1:

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

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

ABS(JULIANDAY(mt1.date) - JULIANDAY(mt2.date)) < 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

and the 2-hours condition would be:

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

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