Design SQLite queue timestamps with an explicit clock rule

By Batu · English technical notes

Also published in our Blogger archive.

Store UTC with an explicit offset

The aware datetime uses timezone.utc and isoformat renders +00:00. The script writes that exact TEXT value through a SQLite placeholder and reads it back, making the created_at representation deterministic. SQLite has no dedicated datetime storage class, so a queue must choose and consistently compare a format.

UTC wall-clock text is not monotonic and cannot order equal values without another key. This fixture does not parse or migrate older timestamp formats.

Example

import sqlite3
from datetime import datetime, timezone
from pathlib import Path
from tempfile import TemporaryDirectory

with TemporaryDirectory() as directory:
    timestamp = datetime(2026, 9, 8, 12, 0, tzinfo=timezone.utc).isoformat()
    connection = sqlite3.connect(Path(directory) / "queue.db")
    connection.execute("CREATE TABLE job(created_at TEXT NOT NULL)")
    connection.execute("INSERT INTO job VALUES (?)", (timestamp,))
    stored = connection.execute("SELECT created_at FROM job").fetchone()[0]
    assert stored == "2026-09-08T12:00:00+00:00"
    print(f"created_at={stored}")

Expected stdout:

created_at=2026-09-08T12:00:00+00:00

Sources

- datetime.isoformat

- SQLite date and time functions

Prepared with AI assistance. The example uses synthetic data; its stated limits apply.