Dates and Times

CREATE TABLE events(
  id         INTEGER PRIMARY KEY,
  at         TEXT NOT NULL DEFAULT (datetime('now')),   -- UTC, 'YYYY-MM-DD HH:MM:SS'
  at_ms      TEXT,                                      -- datetime('now','subsec')
  at_unix    INTEGER NOT NULL DEFAULT (unixepoch()),
  CHECK(at IS strftime('%Y-%m-%d %H:%M:%S', at))        -- rejects malformed text
) STRICT;

SELECT date('now'), time('now'), datetime('now');
-- 2026-09-15|18:42:01|2026-09-15 18:42:01
SELECT unixepoch('now'), julianday('now');
-- 1789497721|2461299.279178
SELECT datetime(1789497721, 'unixepoch');       -- 2026-09-15 18:42:01
SELECT datetime('now', 'subsec');               -- 2026-09-15 18:42:01.482   3.42+
SELECT unixepoch('now','subsec');               -- 1789497721.482

YYYY-MM-DD
YYYY-MM-DD HH:MM
YYYY-MM-DD HH:MM:SS
YYYY-MM-DD HH:MM:SS.SSS
YYYY-MM-DDTHH:MM:SS        (a literal T also works)
HH:MM / HH:MM:SS           (date defaults to 2000-01-01)
now
DDDDDDDDDD                 (a raw Julian day number)

SELECT date('2026-13-45');       -- (NULL)
SELECT date('15/09/2026');       -- (NULL), no locale parsing at all
SELECT datetime('2026-09-15T14:30:00+02:00');   -- 2026-09-15 12:30:00

SELECT date('now','start of month');              -- 2026-09-01
SELECT date('now','start of month','+1 month','-1 day');   -- end of this month
SELECT date('now','weekday 1');                   -- next Monday, or today if Monday
SELECT date('now','-6 days','weekday 1');         -- the Monday of this week
SELECT datetime('now','start of day','+18 hours');-- 18:00 today
SELECT date('now','+1 year','-1 day');
SELECT datetime(1789497721,'unixepoch','localtime');

SELECT date('2026-09-15','+1 month','start of month');  -- 2026-10-01
SELECT date('2026-09-15','start of month','+1 month');  -- 2026-10-01
SELECT date('2026-01-31','+1 month');                   -- 2026-03-03

SELECT date('2026-01-31','+1 month','floor');    -- 2026-02-28   3.46+
SELECT date('2026-01-31','+1 month','ceiling');  -- 2026-03-03   3.46+
SELECT date('2026-01-31','+1 month');            -- 2026-03-03, ceiling is the default
-- Portable clamp: go to the start of the month, add, then take the day
SELECT date('2026-01-31','start of month','+1 month',
            '+' || (CAST(strftime('%d','2026-01-31') AS INTEGER)-1) || ' days');

SELECT strftime('%Y-%m', at) AS month, count(*) FROM events GROUP BY 1;
SELECT strftime('%Y-W%W', 'now');            -- 2026-W37
SELECT strftime('%H:%M', 'now', 'localtime');
SELECT strftime('%d/%m/%Y', at) FROM events; -- reformat for display only
SELECT strftime('%s','now');                 -- '1789497721' as TEXT
SELECT unixepoch('now');                     -- 1789497721 as INTEGER, 3.38+

SELECT CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP;
-- 2026-09-15|18:42:01|2026-09-15 18:42:01
SELECT CURRENT_TIMESTAMP = datetime('now');            -- 1
SELECT CURRENT_TIMESTAMP = datetime('now','localtime');-- 0 outside UTC
SELECT datetime('now'), datetime('now','localtime');
-- 2026-09-15 18:42:01|2026-09-15 11:42:01     (offset is the host's, not fixed)

-- Correct: a UTC default
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
created_at TEXT NOT NULL DEFAULT (datetime('now'))       -- identical
-- Wrong: non-deterministic, and rejected in an index or generated column
created_at TEXT NOT NULL DEFAULT (datetime('now','localtime'))

-- Days between two dates, as a real number
SELECT julianday('2026-12-25') - julianday('2026-09-15');       -- 101.0
-- Whole days
SELECT CAST(julianday('2026-12-25') - julianday('now') AS INTEGER);
-- Seconds, minutes, hours
SELECT unixepoch(b) - unixepoch(a)                AS seconds,
       (unixepoch(b) - unixepoch(a)) / 60         AS whole_minutes,
       (unixepoch(b) - unixepoch(a)) / 3600.0     AS hours
FROM spans;
-- Whole months, calendar-aware
SELECT (strftime('%Y', b) - strftime('%Y', a)) * 12
     + (strftime('%m', b) - strftime('%m', a)) AS months FROM spans;

SELECT timediff('2026-12-25','2026-09-15');
-- +0000-03-10 00:00:00.000
SELECT timediff('2026-09-15','2026-12-25');
-- -0000-03-10 00:00:00.000

SELECT (strftime('%Y', 'now') - strftime('%Y', dob))
     - (strftime('%m-%d', 'now') < strftime('%m-%d', dob)) AS age
FROM people;

-- Per day, month, year
SELECT date(at) AS d, count(*) FROM events GROUP BY 1 ORDER BY 1;
SELECT strftime('%Y-%m', at) AS m, count(*) FROM events GROUP BY 1;
SELECT strftime('%Y', at) AS y, count(*) FROM events GROUP BY 1;

-- Per hour of the day, to find traffic peaks
SELECT strftime('%H', at) AS hh, count(*) FROM events GROUP BY 1 ORDER BY 1;

-- Per ISO week, Monday-based
SELECT date(at, '-6 days', 'weekday 1') AS week_start, count(*)
FROM events GROUP BY 1 ORDER BY 1;

-- 15-minute buckets, via integer division on the epoch
SELECT datetime((unixepoch(at) / 900) * 900, 'unixepoch') AS bucket, count(*)
FROM events GROUP BY 1 ORDER BY 1;

-- Good: a plain range on the column
SELECT * FROM events WHERE at >= '2026-09-01' AND at < '2026-10-01';
SELECT * FROM events WHERE at >= datetime('now','-7 days');

-- Bad: wraps the column, so no index can be used
SELECT * FROM events WHERE strftime('%Y-%m', at) = '2026-09';
SELECT * FROM events WHERE date(at) = '2026-09-15';

-- The index-friendly form of that last one
SELECT * FROM events WHERE at >= '2026-09-15' AND at < '2026-09-16';

-- Rows older than 90 days, for a retention job
DELETE FROM events WHERE at < datetime('now','-90 days');

-- Expiring sessions, with the comparison on the stored side
DELETE FROM sessions WHERE expires_at <= CURRENT_TIMESTAMP RETURNING id;

-- An expression index when you truly need a bucketed lookup
CREATE INDEX idx_events_month ON events(strftime('%Y-%m', at));

-- A generated + indexed day column, cheaper than an expression index to read
ALTER TABLE events ADD COLUMN day TEXT AS (date(at));
CREATE INDEX idx_events_day ON events(day);