CODEWITHSUNDEEP
pythondatetimesqlite
AK Glenn
AK Glenn

Reputation: 17

Sqlite3 between hour x and hour y

Wanting to build a query that retrieves rows hour by hour, over the last 24 hrs. My question is more in regards to why one method works and another does not. I can not find a definitive answer.

Works:

SELECT date, temp 
FROM dht_temps 
WHERE zone = 'outside' 
 and temp <> 0 and date BETWEEN "2016-12-19 04:00:00" AND "2016-12-19 08:00:00"

Does not work:

SELECT date, temp FROM dht_temps 
WHERE zone = 'outside' and temp <> 0 
  and date BETWEEN date('now','-2 hours') 
  AND date('now','-1 hours')

Seems to me that both should produce the same result but that is why I am a hack. I would like to understand the why. Thanks

Upvotes: 0

Views: 60

Answers (1)

Robᵩ
Robᵩ

Reputation: 168616

You should call datetime(), not date(). Notice how, in the example below, using date() fetches zero results while using datetime() fetches one.

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute('create table dht_temps (date TEXT, temp INTEGER, zone TEXT)')
conn.execute('insert into dht_temps values(datetime("now"), 4, "outside")')
conn.execute('insert into dht_temps values(datetime("now", "-30 minutes"), 1, "outside")')
conn.execute('insert into dht_temps values(datetime("now", "-90 minutes"), 2, "outside")')
conn.execute('insert into dht_temps values(datetime("now", "-150 minutes"), 3, "outside")')
l1 = list(conn.execute("""
SELECT date, temp FROM dht_temps 
WHERE zone = 'outside' and temp <> 0 
  and date BETWEEN date('now','-2 hours') 
  AND date('now','-1 hours')
"""))

l2 = list(conn.execute("""
SELECT date, temp FROM dht_temps 
WHERE zone = 'outside' and temp <> 0 
  and date BETWEEN datetime('now','-2 hours') 
  AND datetime('now','-1 hours')
"""))

assert len(l1) == 0
assert len(l2) == 1

Upvotes: 2

Related Questions