Dates & Times in SQLite: strftime, date() & julianday()

There is no separate date type in SQLite. Timestamps are text in ISO-8601 form, '2024-03-15 23:40:00'. Because the biggest unit comes first, plain text comparison orders them chronologically: WHERE created_at >= '2024-03-01' AND created_at < '2024-04-01' selects March.

Pulling parts out. strftime(format, value) formats a timestamp: %Y year, %m month, %d day, %H hour (00-23), %M minute and %w weekday (0 = Sunday). The result is text, so compare it with quotes: strftime('%H', created_at) >= '22' finds activity from 10 pm onward, the classic after-hours filter.

Grouping by a period is just strftime inside GROUP BY. Count or sum per month, day or hour and the shape of the data (including a spike) shows up immediately:

SELECT strftime('%Y-%m', created_at) AS month,
       SUM(amount)                    AS revenue
FROM orders
GROUP BY strftime('%Y-%m', created_at)
ORDER BY month;

Date arithmetic. date(created_at, '+7 days') shifts a date, and the modifiers include '-1 month', 'start of month' and, with datetime(), '+2 hours'. date('now') is today in UTC.

The gap between two timestamps. julianday() converts a timestamp into a day count, so subtracting two of them gives the elapsed days (the fraction is the time of day). Multiply by 24 for hours:

SELECT id,
       (julianday(resolved_at) - julianday(opened_at)) * 24 AS hours_open
FROM tickets
WHERE resolved_at IS NOT NULL;

Pitfall: a date is not the whole day. created_at <= '2024-03-15' excludes everything on the 15th after midnight, because the text '2024-03-15 08:00:00' sorts after '2024-03-15'. To mean 'through the 15th', write created_at < '2024-03-16'.

Be consistent about time zones. SQLite stores exactly the string you gave it and 'now' is UTC, so a mix of local and UTC timestamps produces gaps that look like real events. Convert once, at the edge.