register-sqlite-adapter-to-serialize-datetimes.md

Register SQLite Adapter To Serialize Datetimes

SQLite doesn't have a datetime or timestamp data type, so that information has to be stored as text or a unix epoch int. That means when using Python's sqlite3 module to perform writes I need to tell it what shape to write a datetime value.

I could manually convert datetime values everywhere they are involved in write operations.

# Prepare `sessions` insert payload
session_data = {
    "active": 1 if active else 0,
    "project_id": project_id,
    "start_time": datetime.isoformat(session.start_time),
    "end_time": None,
}

if session.end_time:
    session_data["end_time"] = datetime.isoformat(session.end_time)

# Insert the new active session
cursor = self.conn.execute(
    """
    insert into sessions (active, project_id, start_time, end_time)
    values (:active, :project_id, :start_time, :end_time)
    returning id;
""",
    session_data,
)

I've used datetime.isoformat above which formats datetime objects like so:

>>> datetime.now().isoformat()
'2026-08-28T11:52:04.709907'

I'd like to make two improvements.

1. I want these datetime values to be formatted instead like 2026-08-28T18:15:27.213Z.
2. I want datetime values to be serialized automatically in the specific shape without having to manually convert them everywhere.

I can achieve both of these things by registering an adapter with sqlite3 that handles the serialize of datetime objects.

First, I define a function that can perform the datetime to str conversion. I decided to put this in db.py where I have some other database-specific functions.

from datetime import datetime, timezone

def to_db(dt: datetime) -> str:
    if dt.tzinfo is None or dt.utcoffset() is None:
        raise ValueError(f"Unable to store naive datetime: {dt!r}")
    dt = dt.astimezone(timezone.utc)
    return f"{dt:%Y-%m-%dT%H:%M:%S}.{dt.microsecond // 1000:03d}Z"

Then I register the adapter before creating the connection that gets used for database interactions.

import sqlite3
from datetime import datetime
from pathlib import Path
from sqlite3 import Connection

def initialize_conn(db_file: Path) -> Connection:
    # register adapters
    sqlite3.register_adapter(datetime, to_db)

    conn: Connection = sqlite3.connect(db_file)
    conn.row_factory = sqlite3.Row

    return conn

Then I can run write operations with datetime objects knowing they will be correctly serialized.

with self.conn:
    query = "update sessions set active = :active, end_time = :end_time where active = 1;"
    self.conn.execute(
        query,
        {"active": 0, "end_time": session.end_time},
    )