CODEWITHSUNDEEP
pythondatetimesqlite
Basj
Basj

Reputation: 46423

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

Upvotes: 0

Views: 1615

Answers (1)

Basj
Basj

Reputation: 46423

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

Upvotes: 1

Related Questions