SQLite Basics: Normalize Dates and Measure Elapsed Time

SQLite has date/time functions but no dedicated date/time storage type. A useful first step is choosing one representation and a timezone policy. These examples use fixed timestamps so the output is repeatable, rather than depending on when the tutorial runs. The unixepoch() function requires SQLite 3.38.0 or later.

Prepare a scratch directory

Tested on Linux with Python 3.12.14 and SQLite 3.53.1. You need Bash, Python 3 with SQLite support, a text editor, and mktemp, rm, and rmdir. No additional Python packages are needed. Keep the same terminal open and save files as UTF-8.

start_dir="$PWD"
work_dir="$(mktemp -d)" || exit 1
cd -- "$work_dir" || exit 1

The standalone SQLite CLI was unavailable on the test machine. Save this runner as run-sql.py. Each invocation uses a fresh in-memory database and prints pipe-separated rows, displaying missing values as NULL. The toy database disappears when the connection closes. Put each statement’s ending semicolon at the end of a line, as shown; do not put two complete statements on one line. This is a teaching runner, not a general SQL shell or CSV exporter.

from pathlib import Path
import sqlite3
import sys

connection = sqlite3.connect(":memory:", isolation_level=None)
try:
    statement = ""
    for line in Path(sys.argv[1]).read_text(encoding="utf-8").splitlines(True):
        statement += line
        if sqlite3.complete_statement(statement):
            cursor = connection.execute(statement)
            for row in cursor:
                print("|".join("NULL" if value is None else str(value) for value in row))
            statement = ""
    if statement.strip():
        raise ValueError("Unfinished SQL statement")
finally:
    connection.close()

1. Normalize an offset timestamp to UTC

Save as normalize-time.sql:

SELECT datetime('2026-10-21T09:00:00+09:00') AS utc_time;
SELECT date('2026-10-21T01:00:00+09:00') AS utc_date;
SELECT datetime(0, 'unixepoch') AS epoch_start;

Run:

python3 run-sql.py normalize-time.sql

Output:

2026-10-21 00:00:00
2026-10-20
1970-01-01 00:00:00

The offset is applied before the date or time is returned. In the second query, 1 a.m. at +09:00 is still the previous day in UTC. Decide whether a report groups by UTC dates or by the customer’s local calendar before extracting its date.

The unixepoch modifier explicitly interprets zero as seconds since the Unix epoch. Do not leave the meaning of numeric timestamps to guesswork. These results have no displayed timezone suffix, so record the UTC convention in your application’s data contract.

2. Filter a month with an exclusive upper bound

Save as month-window.sql:

CREATE TABLE events (id INTEGER PRIMARY KEY, occurred_at TEXT NOT NULL);
INSERT INTO events VALUES
    (1, '2026-09-30 23:59:59'),
    (2, '2026-10-01 00:00:00'),
    (3, '2026-10-31 23:59:59'),
    (4, '2026-11-01 00:00:00');
SELECT id, occurred_at FROM events
WHERE occurred_at >= datetime('2026-10-21', 'start of month')
  AND occurred_at < datetime('2026-10-21', 'start of month', '+1 month')
ORDER BY occurred_at, id;
SELECT date('2026-10-21', 'start of month', '+1 month', '-1 day');

Run:

python3 run-sql.py month-window.sql

Output:

2|2026-10-01 00:00:00
3|2026-10-31 23:59:59
2026-10-31

The range includes the first instant of October and excludes the first instant of November. It does not need an invented last possible time such as 23:59:59.999. The final query separately calculates the last calendar date for display.

This text comparison works because every stored timestamp uses the same fixed-width, space-separated UTC format. Mixing offsets, separators, or unpadded fields would invalidate that assumption. Normalize before storage instead of expecting arbitrary timestamp strings to sort chronologically. Keeping the stored column unwrapped also leaves a simple range condition available to an appropriate index.

3. Measure seconds and calendar-day distance

Save as elapsed-time.sql:

SELECT unixepoch('2026-10-21 10:30:00')
     - unixepoch('2026-10-21 09:00:00') AS elapsed_seconds;
SELECT julianday('2026-10-23') - julianday('2026-10-21') AS elapsed_days;
SELECT strftime('%Y-%m', '2026-10-21') AS month_label;
SELECT datetime('not-a-date') AS invalid_input;

Run:

python3 run-sql.py elapsed-time.sql

Output:

5400
2.0
2026-10
NULL

Subtracting integer Unix timestamps gives 5,400 seconds. The date-only Julian-day difference is a floating-point 2.0. Whole-second Unix timestamps are suitable for these inputs; do not discard fractional precision when the application needs subsecond measurements.

The deliberately unparseable value returns NULL. Check inputs before using the result in a calculation or filter, and do not treat permissive date conversion as comprehensive calendar validation. A missing or invalid timestamp should not silently become the epoch or today’s date.

SQLite’s date/time reference documents accepted formats and left-to-right modifier order. Host-local timezone conversion is unsuitable for deterministic tests; these examples avoid localtime and daylight-saving assumptions. For named-zone business schedules, perform timezone-aware conversion in an application layer with the required timezone data.

Clean up

In the same terminal, remove only these example files and the empty scratch directory:

rm -- run-sql.py normalize-time.sql month-window.sql elapsed-time.sql
cd -- "$start_dir" || exit 1
rmdir -- "$work_dir"
unset start_dir work_dir

Acknowledgements

This post was created with assistance from AI.