Published on

TIL: a SQLite TEXT column compared to a number compares as text

TIL: a SQLite TEXT column compared to a number compares as text
Authors
On this page

I wrote a query to find millisecond timestamps, and it failed in a sneaky way. It returned every row:

SELECT id FROM m WHERE ts > 1e12;

The catch was the column type. It was declared TEXT, which gives it text affinity. When you compare a text-affinity column to a plain number, SQLite converts the number to text first. 1e12 becomes '1000000000000.0', and the comparison runs character by character, like sorting words.

Reproducing it

I tested it with two rows: a real millisecond timestamp and the value '5'.

CREATE TABLE m(id INTEGER, ts TEXT);
INSERT INTO m VALUES (1,'1700000000000'),(2,'5');

SELECT id FROM m WHERE ts > 1e12;                    -- 1, 2
SELECT id FROM m WHERE CAST(ts AS INTEGER) > 1e12;   -- 1
SELECT id FROM m WHERE CAST(ts AS INTEGER) < 1e12;   -- 2

The first query matches both rows, including the '5' that's clearly not a millisecond timestamp. As text, '5' sorts after '1000000000000.0', just like "5" comes after "1" in a dictionary. Casting the column makes the comparison numeric, and only row 1 matches.

typeof(ts) returns text, which is the quickest way to see what you're really comparing.

Takeaway

Check the declared column type before you trust a numeric comparison. Cast when you have to. Better still, store timestamps as integers.