Sqlite datetime comparison with a unix timestamp

Question

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)

Answer 1

You use CURRENT_TIMESTAMP when inserting new rows.
This means that in your column the values are not stored as unix timestamps becuase CURRENT_TIMESTAMP returns the current date in the format of YYYY-MM-DD hh:mm:ss.

You can transform the unix timestamp to datetime in the format of YYYY-MM-DD hh:mm:ss with the function datetime() and the unixepoch modifier:

conn.execute("SELECT * FROM data WHERE datetime <= datetime(?, 'unixepoch')", (ts, ))

If your unix timestamp contains milliseconds you must strip them off:

conn.execute("SELECT * FROM data WHERE datetime <= datetime(? / 1000, 'unixepoch')", (ts, ))

Or, you can transform the string datetime in the column datetime to a unix timestamp with the function strftime():

conn.execute("SELECT * FROM data WHERE strftime('%s', datetime) + 0 <= ?", (ts, ))

If you want to store integer values in the column, use strftime() like this:

INSERT INTO data VALUES (strftime('%s', CURRENT_TIMESTAMP) + 0, 'hello')