
- Authors

- Name
- Nadim Tuhin
- @nadimtuhin
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.
Related posts
- My Hermes backup said DONE, but a tar exclude dropped every database
My agent backup script printed DONE and uploaded 346 MB. The first restore showed the databases were never in the archive. One tar exclude did it.
- TIL: 'No module named' after an update meant a stale process
My agent said httpx was missing. It was installed. The running process had started before the update and was still on the old environment.
- TIL: a gitignored sitemap goes stale (93 vs 123 URLs)
My IndexNow script read public/sitemap.xml and sent 93 URLs. The live sitemap had 123. The local copy is gitignored, so it only updates when I build.