Date and Time Functions in SQLite

Learning outcomes

Chapter 4 separated logical dates/times from SQLite storage classes. Here we operate on those representations. SQLite's date/time functions are powerful for offsets, buckets, and elapsed calculations, but they are not a substitute for an application timezone database.

SQLite accepts time-values; it does not add a sixth date storage class

The date/time functions accept ISO-8601-style text, Julian-day numbers, and—when appropriate modifiers are supplied—Unix timestamps. The underlying value is still TEXT, REAL, or INTEGER.

SELECT  typeof('2026-08-12 03:15:00') AS text_type,  typeof(julianday('2026-08-12 03:15:00')) AS jd_type,  typeof(unixepoch('2026-08-12 03:15:00')) AS epoch_type;

The seven core date/time functions answer different questions

SELECT  date('2026-08-12 03:15:42') AS d,  time('2026-08-12 03:15:42') AS t,  datetime('2026-08-12 03:15:42') AS dt,  julianday('2026-08-12 03:15:42') AS jd,  unixepoch('2026-08-12 03:15:42') AS epoch,  strftime('%Y-%m-%d %H:%M', '2026-08-12 03:15:42') AS formatted,  timediff('2026-08-13 05:45:42','2026-08-12 03:15:42') AS human_span;

timediff(A,B) returns a human-oriented calendar span that, when added to B, reaches A. If you need an exact number of seconds or days, subtract unixepoch() or julianday() values instead.

Modifiers form a left-to-right transformation pipeline

SQLite applies modifiers in order. This is powerful, but order is part of the query's meaning. Use explicit modifiers for Unix timestamps and normalize representation at ingestion so later queries stay simple.

SELECT  datetime(1786504500, 'unixepoch') AS from_epoch,  datetime('2026-08-12 03:15:00', '+2 hours') AS plus_two,  date('2026-08-31', '+1 month', 'floor') AS floor_example,  datetime('now','start of day','+1 day') AS next_utc_day_start;

The floor and ceiling modifiers for ambiguous month/year shifts are available in modern SQLite (3.46.0+). Record a minimum SQLite version if your production SQL depends on them.

UTC, localtime, and what SQLite does not know

SQLite date/time functions internally work from time-values and can convert between UTC and the host's local-time rules using localtime or utc modifiers. Core SQLite does not ship an IANA timezone database and does not accept named zones such as America/Toronto as a general conversion facility.

SELECT datetime('2026-08-12 12:00:00','localtime') AS host_local;SELECT datetime('2026-08-12 12:00:00','utc') AS interpreted_local_to_utc;

Those results depend on the machine/process environment. Do not use a development laptop's local conversion as proof that a server, container, browser, or mobile device will interpret civil-time rules identically. For scheduling across real named timezones and DST transitions, keep timezone identity in the application/domain model and use a timezone-aware library.

Elapsed time: choose human-friendly or numerically exact

WITH x(started_at, ended_at) AS (  VALUES ('2026-08-12 03:15:00','2026-08-12 04:45:30'))SELECT  timediff(ended_at, started_at) AS human_span,  unixepoch(ended_at)-unixepoch(started_at) AS elapsed_seconds,  round((julianday(ended_at)-julianday(started_at))*24.0, 3) AS elapsed_hoursFROM x;

The precise elapsed result here is 5,430 seconds, or 1.508 hours. timediff() is for a calendar-aware human description; it can report the same month-based span for intervals containing different numbers of days, so it is not the metric for SLA seconds.

Daily buckets and expiration predicates

DROP TABLE IF EXISTS event_time_lab;CREATE TABLE event_time_lab(  event_id INTEGER PRIMARY KEY,  device_code TEXT NOT NULL,  occurred_at TEXT NOT NULL,  expires_at TEXT NOT NULL);INSERT INTO event_time_lab(device_code,occurred_at,expires_at) VALUES('PUMP-007','2026-08-12T01:15:00Z','2026-08-12T06:00:00Z'),('PUMP-007','2026-08-12T18:20:00Z','2026-08-13T06:00:00Z'),('FAN-014','2026-08-13T02:10:00Z','2026-08-13T03:00:00Z');SELECT date(occurred_at) AS utc_day, count(*) AS eventsFROM event_time_labGROUP BY date(occurred_at)ORDER BY utc_day;SELECT event_id, device_code, expires_atFROM event_time_labWHERE unixepoch(expires_at) <= unixepoch('2026-08-13T04:00:00Z')ORDER BY event_id;

The bucket works lexically/functionally because every stored timestamp uses one normalized UTC convention. If rows mix offsets, bare dates, local timestamps, and epochs, one expression cannot safely infer the intended chronology.

Lexical ordering is safe only under a representation contract

WITH mixed(ts) AS (  VALUES    ('2026-08-12T09:00:00+09:00'),    ('2026-08-12T01:30:00Z'))SELECT ts FROM mixed ORDER BY ts;

The strings sort by characters, not by converted instants. The +09:00 value represents 00:00Z and is earlier chronologically, yet textual ordering can place it after the 01:30Z string. Normalize before storing or compare through an explicit conversion such as unixepoch().

Time-value lab

WITH jobs(job_id,started_at,finished_at,ttl_seconds) AS (  VALUES    (1,'2026-08-12T01:00:00Z','2026-08-12T01:12:30Z',900),    (2,'2026-08-12T23:55:00Z','2026-08-13T00:25:00Z',1200),    (3,'2026-08-13T03:00:00Z',NULL,1800))SELECT job_id,       date(started_at) AS start_day,       CASE WHEN finished_at IS NULL THEN NULL            ELSE unixepoch(finished_at)-unixepoch(started_at) END AS elapsed_seconds,       datetime(started_at, printf('+%d seconds', ttl_seconds)) AS expires_at,       CASE WHEN finished_at IS NULL THEN 'running'            WHEN unixepoch(finished_at)-unixepoch(started_at) <= ttl_seconds THEN 'within-ttl'            ELSE 'late' END AS outcomeFROM jobsORDER BY job_id;

Expected outcomes are within-ttl for job 1, late for job 2, and running for job 3. The SQL makes the missing-finish rule explicit instead of silently treating NULL as zero seconds.

Summary and bridge

Time correctness comes from a representation contract first and functions second. Normalize formats, state whether timestamps are UTC or civil time, preserve named-zone identity outside core SQLite when required, and use numeric differences for precise elapsed metrics. Lesson 4 uses CTEs to organize multi-step queries and introduces safe recursive traversal.

Authoritative references

• SQLite — Date And Time Functions.
• SQLite — Datatypes In SQLite.
• SQLite — Release History (3.43 timediff; 3.46 date enhancements).