Date and Time Functions

The five date functions and their modifiers

SQLite has five date/time functions, all of which accept the same set of modifiers for arithmetic. Memorize the modifiers; the functions are easy.

date(time, modifiers...) — returns YYYY-MM-DD.
time(time, modifiers...) — returns HH:MM:SS.
datetime(time, modifiers...) — returns YYYY-MM-DD HH:MM:SS.
julianday(time, modifiers...) — Julian day as REAL.
strftime(format, time, modifiers...) — custom format string.
unixepoch(time, modifiers...) (3.38+) — seconds since 1970.

Useful modifiers:

'+N days', '-N hours', '+N minutes', etc.
'start of month', 'start of year', 'start of day'.
'weekday N' where 0=Sunday, 6=Saturday — moves forward to the next weekday N.
'localtime', 'utc' — timezone shift.

Tip: datetime('now') always returns UTC. If you store local time, store the offset too or convert to UTC at write time. Mixed-timezone columns are a permanent source of bugs.

Code

Common date queries·sql

-- Yesterday
SELECT date('now', '-1 day');

-- Start of this month
SELECT date('now', 'start of month');

-- Next Monday
SELECT date('now', 'weekday 1');

-- Format an existing column
SELECT strftime('%Y-%m', created_at) AS year_month, count(*)
FROM   messages GROUP BY year_month ORDER BY year_month;

-- Last 7 days of activity
SELECT * FROM messages
WHERE  created_at >= datetime('now', '-7 days');

-- Round-trip text -> epoch -> text
SELECT datetime(unixepoch('2026-05-03 12:00:00'), 'unixepoch');

Exercise

On the messages table, write five date queries: (1) messages from today, (2) messages from this calendar week, (3) per-month counts for the last 6 months, (4) the median time-of-day messages are sent (pick any reasonable definition), (5) the longest gap between any two consecutive messages in a single conversation. Use only built-in date functions.

The five date functions and their modifiers

Code

External links

Exercise

Progress

Comments 0