The Sqlite documentation states:
SQLite has no DATETIME datatype. Instead, dates and times can be stored in any of these ways:
- As a TEXT string in the ISO-8601 format. Example: '2018-04-02 12:13:46'.
- As an INTEGER number of seconds since 1970 (also known as "unix time").
...
so I decided to use an INTEGER unix timestamp:
import sqlite3, time
conn = sqlite3.connect(':memory:')
conn.execute("CREATE TABLE data(datetime INTEGER, t TEXT);")
conn.execute("INSERT INTO data VALUES (CURRENT_TIMESTAMP, 'hello')")
Why does the following query return no result?
ts = int(time.time()) + 31*24*3600 # unix timestamp 1 month in the future
print(list(conn.execute("SELECT * FROM data WHERE datetime <= ?", (ts, ))))
More generally, how to do a SELECT query with a comparison with a unix timestamp with Sqlite?
PS:
I have already read SQLite DateTime comparison and similar questions, which offer other comparison methods, but here I'd like to precisely discuss why this unix timestamp comparison does not work.
For performance reasons, I'd like to:
- do a query that compares integers (which is super fast if many rows):
WHERE datetime <= unix_timestamp,
- avoid to convert
unix_timestamp into string, and then compare datetime to this string (I guess it'll be far slower)