DATETIME comparisons (and presumably other DATETIME operations) silently degrade to operations on text, and this could spoil your weekend if you don’t spot it.
A demo
For the record, I’m running SQLite 3.39.5.
Let’s start with a very simple table with a DATETIME column:
create table foo (d datetime not null);
We insert the current timestamp. Behind the scenes I think that SQLite stores this as text. It will end up with a form like ‘2023-08-21 18:57:33’.
insert into foo (d) values (datetime('now'));
Next, we’ll insert an ISO-8601 (or SQLite-compatible subset thereof) date-time. I checked: this is a valid way to represent a DATETIME in SQLite. We construct it from component parts; it’ll end up with a form similar to the plain date-time above, but with a T separating the date and time, e.g. ‘2023-08-21T18:57:33’.
insert into foo (d) values (date('now') || 'T' || time('now'));
We have a table with a single column and two rows:
| d |
-----------------------
| 2023-08-21 19:03:17 |
| 2023-08-21T19:03:23 |
It’s now trivial to reproduce the problem:
sqlite> select d, d <= datetime('now') from foo;
2023-08-21 19:03:17 | 1
2023-08-21T19:03:23 | 0
i.e. SQLite thinks the ISO-8601 date-time is in the future.
Digging deeper
Let’s investigate further. We can use datetime function modifiers to search for a date-time that is ahead of 2023-08-21T19:03:23:
sqlite> select d, d <= datetime('now', '+10 hours') from foo where d like '%T%';
2023-08-21T19:03:23 | 1
sqlite> select d, d <= datetime('now', '+2 hours') from foo where d like '%T%';
2023-08-21T19:03:23 | 0
sqlite> select d, d <= datetime('now', '+6 hours') from foo where d like '%T%';
2023-08-21T19:03:23 | 1
sqlite> select d, d <= datetime('now', '+4 hours') from foo where d like '%T%';
2023-08-21T19:03:23 | 0
sqlite> select d, d <= datetime('now', '+5 hours') from foo where d like '%T%';
2023-08-21T19:03:23 | 1
Huh! Something changes between 4 and 5 hours from now! Well, yes: it’ll be tomorrow:
sqlite> select datetime('now', '+5 hours');
2023-08-22 00:12:20
Ummmmm 🤔 SQLite appears to be silently degrading to textual comparisons of DATETIME columns when they contain ISO-8601 strings – which are valid according to the documentation. It still performs proper date-time comparisons on values that don’t have that T in them.
Won’t get fooled again (yes I will)
A workaround is to always retell SQLite that it’s a DATETIME:
sqlite> select d, datetime(d) <= datetime('now') from foo;
2023-08-21 19:03:17 | 1
2023-08-21T19:03:23 | 1
⚠️ But SQLite will not complain if I (or you) forget – and so I (and you) will inevitably forget.
I would have so much preferred if SQLite had refused to do the comparison instead of something “helpful” but actually misleadingly useless/harmful instead.