SQLite Date and Time: strftime, date(), datetime(), and Modifiers

SQLite Doesn't Really Have a Date Type

This trips up everyone coming from Postgres or MySQL. SQLite has five storage classes — NULL, INTEGER, REAL, TEXT, BLOB — and that's it. There is no DATE, no DATETIME, no TIMESTAMP. You can write created_at DATETIME in your CREATE TABLE, and SQLite will accept it, but it stores the value as plain text or a number.

What SQLite does give you is a set of functions that understand three conventional formats:

• ISO 8601 text — '2026-04-23', '2026-04-23 10:15:00', '2026-04-23T10:15:00.123Z'.
• Unix timestamp — seconds since 1970-01-01 UTC, stored as an integer.
• Julian day number — fractional days since 4714 BC, stored as a real.

Pick one and stick with it. ISO 8601 text is the most readable and sorts correctly as a string, which is why it's the default.

SELECT date('now'),
       datetime('now'),
       strftime('%s', 'now'),
       julianday('now');

Four ways to ask for "right now" — text date, text datetime, Unix seconds, Julian day. They all describe the same instant.

The Five Date Functions

SQLite gives you five built-in functions that cover almost everything:

• date(time, ...) — returns YYYY-MM-DD.
• time(time, ...) — returns HH:MM:SS.
• datetime(time, ...) — returns YYYY-MM-DD HH:MM:SS.
• julianday(time, ...) — returns a real (great for differences).
• strftime(format, time, ...) — returns a custom-formatted string.

Each one takes a time value as the first argument, then any number of modifier strings.

SELECT date('2026-04-23 10:15:00')          AS just_date,
       time('2026-04-23 10:15:00')          AS just_time,
       datetime(1745402100, 'unixepoch')    AS from_unix,
       julianday('2026-04-23')              AS julian;

Note 'unixepoch' — that's how you tell the date functions that the input integer is a Unix timestamp rather than a Julian day. Without it, SQLite assumes the number is Julian.

strftime: Custom Date Formats

strftime is the workhorse. Same %-codes you'd recognize from C or Python:

SELECT strftime('%Y-%m-%d',           'now') AS iso_date,
       strftime('%d/%m/%Y',           'now') AS european,
       strftime('%H:%M',              'now') AS hours_minutes,
       strftime('%Y-%m-%dT%H:%M:%fZ', 'now') AS iso_with_ms,
       strftime('%w',                 'now') AS day_of_week,
       strftime('%j',                 'now') AS day_of_year;

The codes you'll reach for most:

• %Y — four-digit year.
• %m — month (01-12).
• %d — day of month (01-31).
• %H, %M, %S — hours, minutes, seconds.
• %w — day of week (0=Sunday).
• %j — day of year (001-366).
• %s — Unix timestamp.
• %f — fractional seconds (SS.SSS).

strftime is also how you extract parts of a date — there's no separate EXTRACT or YEAR() function. You just format it down to the part you want and cast if you need a number:

SELECT strftime('%Y', '2026-04-23') AS year_text,
       CAST(strftime('%Y', '2026-04-23') AS INTEGER) AS year_num,
       CAST(strftime('%m', '2026-04-23') AS INTEGER) AS month_num;

strftime always returns text, so wrap it in CAST(... AS INTEGER) when you want to do arithmetic or compare numerically.

Modifiers: Date Math Without an Operator

This is the feature that makes SQLite's date handling pleasant. After the time argument, you can pass any number of modifier strings, and they apply in order:

SELECT date('2026-04-23', '+7 days')              AS week_later,
       date('2026-04-23', '-1 month')             AS month_earlier,
       datetime('now', '+2 hours', '+30 minutes') AS in_two_thirty,
       date('now', 'start of month')              AS first_of_month,
       date('now', 'start of month', '+1 month', '-1 day') AS last_of_month,
       date('now', 'weekday 0')                   AS next_sunday;

The modifiers you'll use constantly:

• '+N days', '-N days', and the same for hours, minutes, seconds, months, years.
• 'start of day', 'start of month', 'start of year' — truncate to that boundary.
• 'weekday N' — advance to the next given weekday (0=Sunday).
• 'localtime' and 'utc' — convert between time zones.

The "last day of the month" trick — start of month, plus one month, minus one day — is worth memorizing. SQLite has no LAST_DAY function, but modifier chaining gives you the same thing.

UTC vs. Local Time

'now' always returns UTC. If you want local time, you have to ask:

SELECT datetime('now')              AS utc_now,
       datetime('now', 'localtime') AS local_now,
       datetime('2026-04-23 10:00:00', 'utc') AS treated_as_local_then_utc;

The 'localtime' modifier converts a UTC value to the system's local zone. The 'utc' modifier does the reverse — it treats the input as local time and converts to UTC.

The safe habit: store everything as UTC, convert to local only when displaying. Mixing zones in storage leads to bugs that show up twice a year when daylight saving shifts.

Comparing Dates and Filtering Ranges

If you store dates as ISO 8601 text, comparison and BETWEEN just work — ISO 8601 sorts lexicographically the same way it sorts chronologically. That's the whole reason it's the default format.

CREATE TABLE orders (
    id         INTEGER PRIMARY KEY,
    customer   TEXT,
    placed_at  TEXT NOT NULL  -- ISO 8601
);

INSERT INTO orders (customer, placed_at) VALUES
    ('Ada',   '2026-04-01 09:30:00'),
    ('Boris', '2026-04-15 14:00:00'),
    ('Carla', '2026-04-23 11:45:00'),
    ('Dmitri','2026-05-02 08:15:00');

SELECT customer, placed_at
FROM orders
WHERE placed_at >= '2026-04-01'
  AND placed_at <  '2026-05-01'
ORDER BY placed_at;

The half-open range (>= start, < end) is a useful habit — it avoids the "did midnight on the 30th get included or not?" question entirely.

For "the last 7 days," let SQLite compute the boundary:

CREATE TABLE orders (
    id         INTEGER PRIMARY KEY,
    customer   TEXT,
    placed_at  TEXT NOT NULL
);

INSERT INTO orders (customer, placed_at) VALUES
    ('Ada',   datetime('now', '-2 days')),
    ('Boris', datetime('now', '-10 days')),
    ('Carla', datetime('now', '-1 day')),
    ('Dmitri',datetime('now', '-30 days'));

SELECT customer, placed_at
FROM orders
WHERE placed_at >= datetime('now', '-7 days')
ORDER BY placed_at DESC;

Date Differences

SQLite has no DATEDIFF. Two patterns cover everything:

SELECT julianday('2026-04-23') - julianday('2026-04-01')           AS days_between,
       (julianday('2026-04-23 18:00') - julianday('2026-04-23 09:00')) * 24 AS hours_between,
       strftime('%s', '2026-04-23 18:00') - strftime('%s', '2026-04-23 09:00') AS seconds_between;

julianday() differences are in days (with fractional precision), so multiplying by 24 gives hours, by 1440 gives minutes. strftime('%s', ...) differences are in seconds — convenient when you want an integer.

CAST(... AS INTEGER) truncates fractional days if you want whole-day counts:

CREATE TABLE orders (
    id         INTEGER PRIMARY KEY,
    customer   TEXT,
    placed_at  TEXT NOT NULL
);

INSERT INTO orders (customer, placed_at) VALUES
    ('Ada',   datetime('now', '-2 days')),
    ('Boris', datetime('now', '-10 days')),
    ('Carla', datetime('now', '-1 day')),
    ('Dmitri',datetime('now', '-30 days'));

SELECT customer,
       CAST(julianday('now') - julianday(placed_at) AS INTEGER) AS days_ago
FROM orders
ORDER BY days_ago;

Storing Dates: Pick One Format and Commit

Three reasonable choices, in order of how often you should pick them:

1. ISO 8601 text (TEXT). Readable in dumps, sorts correctly, plays nicely with every date function. Default choice.
2. Unix seconds (INTEGER). Compact, fast comparisons, no time-zone ambiguity. Good when you have millions of rows. You'll need datetime(col, 'unixepoch') to read it back.
3. Julian day (REAL). Rarely worth it unless you're doing heavy date arithmetic and want subsecond precision in one column.

What you should not do is mix formats in the same column. The date functions will quietly accept either, but indexes, sorts, and comparisons will produce nonsense.

-- Defaults that auto-fill on insert:
CREATE TABLE events (
    id          INTEGER PRIMARY KEY,
    name        TEXT NOT NULL,
    happened_at TEXT NOT NULL DEFAULT (datetime('now'))
);

INSERT INTO events (name) VALUES ('signup'), ('login');

SELECT name, happened_at FROM events;

DEFAULT (datetime('now')) is the SQLite equivalent of DEFAULT CURRENT_TIMESTAMP — it stamps every new row with the current UTC time without any application code involved.

Grouping by Time Period

strftime shines when you want to bucket rows by month, week, or hour:

CREATE TABLE orders (
    id         INTEGER PRIMARY KEY,
    customer   TEXT,
    placed_at  TEXT NOT NULL
);

INSERT INTO orders (customer, placed_at) VALUES
    ('Ada',   '2026-04-01 09:30:00'),
    ('Boris', '2026-04-15 14:00:00'),
    ('Carla', '2026-04-23 11:45:00'),
    ('Dmitri','2026-05-02 08:15:00'),
    ('Ezra',  '2026-05-20 17:00:00');

SELECT strftime('%Y-%m', placed_at) AS month,
       COUNT(*)                     AS order_count
FROM orders
GROUP BY month
ORDER BY month;

Same idea for "orders per hour of day," "signups per weekday," or "events per minute" — pick a format string that captures only the granularity you want, group by that, and aggregate.

Next: Aggregate Functions

Speaking of grouping — that COUNT(*) is the simplest of SQLite's aggregate functions. Next we'll look at the full set: SUM, AVG, MIN, MAX, and how they collapse many rows into one summary value.