Dates, Booleans, and Practical Types

The two types SQLite doesn't have

SQLite has no native DATE/DATETIME type and no native BOOLEAN. Both are conventions over the existing types. There are three viable approaches for dates:

ISO 8601 TEXT — '2026-05-03 12:00:00'. Human-readable, sorts correctly as text, supported by all SQLite date functions. Recommended default.
Unix epoch INTEGER — seconds since 1970. Compact, fast to compare, easy in any language. Use when you need numeric range filtering at scale.
Julian Day REAL — astronomical convention. Pretty much never the right choice for application code.

For booleans, SQLite stores 0/1 as integers and accepts the literals TRUE and FALSE (which compile to 1 and 0). You'll see this everywhere: archived INTEGER NOT NULL DEFAULT 0.

Tip: Pippa's conversations table uses ISO 8601 text for created_at / updated_at because the JSONL ground truth is also ISO 8601. The two stay byte-comparable, which makes purge-and-replay reconstruction simple.

Code

Date functions that matter·sql

SELECT datetime('now');                       -- 2026-05-03 12:00:00
SELECT date('now');                           -- 2026-05-03
SELECT strftime('%Y-%m-%dT%H:%M:%fZ', 'now'); -- 2026-05-03T12:00:00.000Z
SELECT unixepoch('now');                       -- 1747900800

-- Compare across formats
SELECT datetime(unixepoch('now'), 'unixepoch'); -- round-trip back to text

-- Filter recent rows
SELECT * FROM messages
WHERE  created_at >= datetime('now', '-1 day');

Booleans — store as INTEGER, query naturally·sql

CREATE TABLE flags (id INTEGER PRIMARY KEY, archived INTEGER NOT NULL DEFAULT 0) STRICT;

INSERT INTO flags(archived) VALUES (TRUE);   -- stored as 1
INSERT INTO flags(archived) VALUES (FALSE);  -- stored as 0

SELECT * FROM flags WHERE archived;          -- WHERE 1 -> matches
SELECT * FROM flags WHERE NOT archived;      -- matches the 0 row

Exercise

On a small events table with a created_at column, demonstrate three date storage strategies — ISO 8601 text, Unix epoch integer, and Julian Day. For each, write the same 'rows from the last 24 hours' query. Time them on a 100,000-row table. Then write a one-paragraph recommendation for which one you'd default to and why.

Dates, Booleans, and Practical Types

The two types SQLite doesn't have

Code

External links

Exercise

Progress

Comments 0