2

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)
Basj
  • 41,386
  • 99
  • 383
  • 673
  • I dont know if this will be helpful but when I use unix time in miliseconds, as an int then it will make an overflow, long is better. – mama Dec 21 '20 at 20:30
  • @mama Does Sqlite use unix timestamps in milliseconds or seconds? – Basj Dec 21 '20 at 20:31
  • python does use seconds as default. idk about sqlite, but normally default is milliseconds – mama Dec 21 '20 at 20:32
  • @DinoCoderSaurus Why? The row is inserted with current timestamp. So querying with `datetime <= timestamp_1_month_in_the_future` should work. – Basj Dec 21 '20 at 20:33
  • @DinoCoderSaurus IIRC, it also contains the HH:MM:SS and possibly milliseconds? Do you have a source about this? – Basj Dec 21 '20 at 20:40

1 Answers1

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')
forpas
  • 160,666
  • 10
  • 38
  • 76
  • Thanks @forpas. For performance reasons, I'd like to do a query that compares *integers* (which is super fast if many rows): `WHERE datetime <= unix_timestamp`, and avoid to convert `unix_timestamp` into string, and then compare datetime to this string (I guess it'll be far slower?). What do you think? – Basj Dec 21 '20 at 20:41
  • *CURRENT_TIMESTAMP returns the current date in the format of YYYY-MM-DD hh:mm:ss* oh this might be the reason, do you have a source/link for this, for future reference? Also, how could I get the current timestamp as unix timetamp instead of string? – Basj Dec 21 '20 at 20:44
  • Thanks @forpas! PS: Why the `+ 0`? – Basj Dec 21 '20 at 20:51
  • strftime() returns a string and +0 does an implicit conversion to an integer. – forpas Dec 21 '20 at 20:52