ISO-8601 and DATETIME in SQLite

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.