Sqlite datetime hour difference

Question

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(':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),))

This query works:

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

and returns:

(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))

but the datetime difference computation doesn't work:

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

(0,)
(0,)
...

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.

How to do this?

Note:

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

• 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)
  

Answer 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).