Dates in sqlite
Published on . Updated on
Recently I discovered I had been over-thinking dates in SQLite, since the docs say “SQLite does not have a storage class set aside for storing dates and/or times” and so I wrongly thought this meant that the following was impossible: select * from blah were date between '2020-01-01' and '2020-01-02' Turns out, it is supported, and is made possible by the behavior that date strings stored as TEXT as YYYY-MM-DD (since it is one of the “Time Values“) have the affinity NUMERIC and thus comparisons work! (Also note that BETWEEN works since it becomes expr1 >= expr2 AND expr1 <= expr3.)
But the “Date and Time Datatype” section of the docs doesn’t mention affinity. It continues that “SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values.” Perhaps I should have inferred from the “TEXT as ISO8601” strings that simple ‘YYYY-MM-DD’ is supported, but I didn’t.
So then I was left thinking about juliandays and timestamps, and the added overhead of lots of DATE() calls in SQL queries. Perhaps this is a good thing, because then I also pondered storage implications. A ‘YYYY-MM-DD’ string is 80-bits in UTF8, while the equivalent Julian Day REAL representation fits into 64-bits and the timestamp into 32-bits. That’s more than double, and is much worse for datetimes.
But, say you take this advice, you’re back to SELECT * FROM FOO WHERE DATE BETWEEN A AND B doesn’t work again, ugh! The fix isn’t terrible, just DATE(DATE) or DATE(DATE, 'unixepoch') whether you’ve chosen julianday or timestamps respectively, but that is hard for me to remember and it turns out there’s a better way made possible in version 3.31: generated columns! Now you can simply add the following to your schema to always have a nice date column to work with!
ALTER TABLE FOO ADD date TEXT GENERATED ALWAYS AS (date(timestamp, 'unixepoch')) VIRTUAL