Date and Time Functions in SQLite

SQLite has no dedicated date or time storage class. Its five storage classes are NULL, INTEGER, REAL, and TEXT, plus BLOB. You can write created_at DATETIME in a CREATE TABLE and SQLite accepts it, but the column is just text or a number underneath. Dates work through a set of functions that interpret values as time, not through a special type.

This trips up newcomers, but it's workable once you pick a storage format and stick to it.

The six functions

SQLite provides six date and time functions:

• date(time-value, modifiers...) returns YYYY-MM-DD
• time(time-value, modifiers...) returns HH:MM:SS
• datetime(time-value, modifiers...) returns YYYY-MM-DD HH:MM:SS
• julianday(time-value, modifiers...) returns a floating-point Julian day number
• unixepoch(time-value, modifiers...) returns Unix seconds as an integer (added 3.38.0, 2022)
• strftime(format, time-value, modifiers...) returns a custom-formatted string

All of them take a time value followed by zero or more modifiers. If you omit the time value entirely from date(), time(), and datetime(), they default to 'now'.

SELECT date('now');            -- 2026-06-02
SELECT time('now');            -- current UTC time
SELECT datetime('now');        -- 2026-06-02 17:00:00
SELECT strftime('%Y-%m-%d %H:%M', 'now');

A point that surprises people: 'now' and all these functions operate in UTC by default, not local time. To get local time, add the 'localtime' modifier (covered below).

Accepted time values

The time value can be any of several formats:

SELECT date('2026-06-02');            -- ISO 8601 date
SELECT datetime('2026-06-02 14:30');  -- ISO date and time
SELECT datetime(1780358400, 'unixepoch');   -- 2026-06-02 00:00:00 (Unix seconds)
SELECT datetime(2460829.5);           -- a raw number is read as a Julian day
SELECT datetime('now');               -- the literal string 'now'

The key subtlety: a bare number like 1780358400 is interpreted as a Julian day, not a Unix timestamp. To read a Unix timestamp you must add the 'unixepoch' modifier. Forgetting this produces dates thousands of years off, which is one of the most common SQLite date bugs.

strftime format codes

strftime() is the workhorse for both formatting and extracting parts of a date. Common codes:

-- extract just the year
SELECT strftime('%Y', '2026-06-02');   -- 2026
 
-- group sales by month
SELECT strftime('%Y-%m', order_date) AS month, SUM(total)
FROM orders
GROUP BY month
ORDER BY month;

Note that strftime('%s', ...) returns the Unix timestamp as text. If you want it as an integer, unixepoch() is cleaner: SELECT unixepoch('2026-06-02');.

Modifiers

Modifiers transform the time value, and they chain left to right. The arithmetic modifiers:

SELECT date('now', '+1 day');         -- tomorrow
SELECT date('now', '-7 days');        -- a week ago
SELECT date('now', 'start of month', '+1 month', '-1 day'); -- last day of this month
SELECT datetime('now', '+1 hour', '+30 minutes');

The structural modifiers snap a date to a boundary:

SELECT date('now', 'start of month');         -- first of this month
SELECT datetime('now', 'start of day');       -- midnight today
SELECT date('now', 'start of year', '+9 months'); -- first of October

'weekday N' advances to the next given weekday (0=Sunday). It moves forward to the next occurrence, or stays put if today already matches:

SELECT date('now', 'weekday 1');   -- next Monday (or today if Monday)

And the conversion modifiers:

SELECT datetime('now', 'localtime');    -- convert UTC 'now' to local time
SELECT datetime('2026-06-02 12:00', 'utc'); -- treat input as local, convert to UTC
SELECT datetime(1780358400, 'unixepoch'); -- read the value as Unix seconds
SELECT datetime(1780358400, 'unixepoch', 'localtime'); -- and show it local

Calculating differences

There is no DATEDIFF function. You compute differences by converting both values to a comparable number and subtracting.

-- whole days between two dates, via Julian day
SELECT julianday('2026-06-02') - julianday('2026-01-01');  -- 152.0
 
-- seconds between two timestamps, via unixepoch
SELECT unixepoch('2026-06-02 12:00') - unixepoch('2026-06-02 11:30'); -- 1800
 
-- age in years (approximate, ignores leap-year edge cases)
SELECT (julianday('now') - julianday(birth_date)) / 365.25 FROM people;

For exact calendar-aware year/month differences, extract the parts with strftime and do the arithmetic yourself, because Julian-day division gives you elapsed time, not calendar boundaries.

How to store dates

The format you store determines how easily you can query. Three workable choices:

• ISO 8601 text ('2026-06-02 14:30:00'): human-readable, sorts correctly as text, works directly with every date function. The most common choice. Always store UTC to avoid timezone ambiguity.
• Unix timestamp as INTEGER: compact, fast to compare and index, but you must remember the 'unixepoch' modifier on every read. Good for high-volume timestamp columns.
• Julian day as REAL: what the functions use internally; rarely the right choice for application data because it's unreadable.

Pick one per column and be consistent. Mixing formats in a single column is the surest way to get silently wrong results.

Common mistakes

• Forgetting 'unixepoch'. A bare integer is read as a Julian day, not Unix seconds. Dates land in the wrong millennium.
• Assuming local time. All functions default to UTC. Add 'localtime' only at display time, and store UTC.
• Expecting a DATEDIFF. It doesn't exist. Use julianday() or unixepoch() subtraction.
• Comparing inconsistently formatted text. '2026-6-2' does not sort or compare like '2026-06-02'. Zero-pad, or store ISO 8601 consistently.
• Using BETWEEN on a date column with time components. BETWEEN '2026-06-01' AND '2026-06-02' excludes most of June 2nd, since '2026-06-02' means midnight. Use < '2026-06-03' instead.

For grouping and rolling-window date math, see our SQLite window functions guide.

When you're chaining several modifiers and aren't sure of the order, Mako's AI autocomplete can assemble the strftime format string and modifier list so you can check the output against real rows.

Mako connects to SQLite and eight other databases with AI-powered autocomplete. Try it free at mako.ai.