ZetCode

Python sqlite3 dates

last modified November 19, 2026 Python 3.15

In this article we store dates and times in SQLite databases with the sqlite3 module. SQLite has no date or time column type. It stores dates as text, as integers, or as real numbers, and its date functions understand all three. Which form we choose, and how Python converts the values, decides whether queries sort, filter, and group correctly.

The basics of the sqlite3 module are covered in the SQLite Python tutorial. The examples use in-memory databases and were run with SQLite 3.53 and the local time zone set to Europe/Bratislava.

Dates as ISO text

The simplest approach converts dates to ISO 8601 strings ourselves.

iso_text.py
import sqlite3
from datetime import date

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE tasks(name TEXT, due TEXT)")

due = date(2026, 12, 4)
con.execute("INSERT INTO tasks VALUES (?, ?)", ("report", due.isoformat()))

name, text = con.execute("SELECT name, due FROM tasks").fetchone()
print(name, repr(text))

value = date.fromisoformat(text)
print(value, value.strftime("%A"))
con.close()

The isoformat method gives the text 2026-12-04, which goes into a TEXT column. The query returns the string unchanged, and date.fromisoformat turns it back into a date. ISO strings sort in chronological order and are understood by SQLite's date functions.

$ python iso_text.py
report '2026-12-04'
2026-12-04 Friday

The deprecated default adapters

The sqlite3 module used to convert date and datetime objects automatically. These default adapters and converters are deprecated since Python 3.12.

default_adapters.py
import sqlite3
from datetime import date, datetime

con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)
con.execute("CREATE TABLE log(day DATE, created TIMESTAMP)")

con.execute("INSERT INTO log VALUES (?, ?)",
            (date(2026, 11, 19), datetime(2026, 11, 19, 14, 30)))

print(con.execute("SELECT typeof(day), created FROM log").fetchone())
print(con.execute("SELECT * FROM log").fetchone())
con.close()

Passing date objects as parameters still works, and with PARSE_DECLTYPES the declared column types DATE and TIMESTAMP convert the values back. But each use issues a DeprecationWarning, and the defaults ignore time zones. The typeof function shows that SQLite stored the date as text.

$ python default_adapters.py
/home/user/python/default_adapters.py:7: DeprecationWarning: The default date adapter is deprecated as of Python 3.12; see the sqlite3 documentation for suggested replacement recipes
  con.execute("INSERT INTO log VALUES (?, ?)",
/home/user/python/default_adapters.py:7: DeprecationWarning: The default datetime adapter is deprecated as of Python 3.12; see the sqlite3 documentation for suggested replacement recipes
  con.execute("INSERT INTO log VALUES (?, ?)",
/home/user/python/default_adapters.py:10: DeprecationWarning: The default timestamp converter is deprecated as of Python 3.12; see the sqlite3 documentation for suggested replacement recipes
  print(con.execute("SELECT typeof(day), created FROM log").fetchone())
('text', datetime.datetime(2026, 11, 19, 14, 30))
/home/user/python/default_adapters.py:11: DeprecationWarning: The default date converter is deprecated as of Python 3.12; see the sqlite3 documentation for suggested replacement recipes
  print(con.execute("SELECT * FROM log").fetchone())
/home/user/python/default_adapters.py:11: DeprecationWarning: The default timestamp converter is deprecated as of Python 3.12; see the sqlite3 documentation for suggested replacement recipes
  print(con.execute("SELECT * FROM log").fetchone())
(datetime.date(2026, 11, 19), datetime.datetime(2026, 11, 19, 14, 30))

Registering adapters

An adapter converts a Python object into a value SQLite can store. The register_adapter function registers one per type.

adapters.py
import sqlite3
from datetime import date, datetime

sqlite3.register_adapter(date, date.isoformat)
sqlite3.register_adapter(datetime, datetime.isoformat)

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE events(title TEXT, day DATE, starts DATETIME)")

rows = [
    ("kickoff", date(2026, 11, 23), datetime(2026, 11, 23, 9, 0)),
    ("review", date(2026, 11, 27), datetime(2026, 11, 27, 14, 30, 15, 500)),
]
con.executemany("INSERT INTO events VALUES (?, ?, ?)", rows)

for row in con.execute("SELECT * FROM events"):
    print(row)

print(con.execute("SELECT date(starts), time(starts) FROM events").fetchall())
con.close()

The adapters call isoformat, so dates and datetimes can be passed directly as parameters. The DATE and DATETIME column types are only names here; the values are stored as text, as the query shows. Note the T separator of isoformat, while the old default adapter used a space. Both are valid for SQLite's functions, such as date and time, but they do not compare correctly with each other as text, so one database should use one format.

$ python adapters.py
('kickoff', '2026-11-23', '2026-11-23T09:00:00')
('review', '2026-11-27', '2026-11-27T14:30:15.000500')
[('2026-11-23', '09:00:00'), ('2026-11-27', '14:30:15')]

Registering converters

A converter turns stored values back into Python objects. It is chosen by the declared type of the column when the connection uses PARSE_DECLTYPES.

converters.py
import sqlite3
from datetime import date, datetime

sqlite3.register_adapter(date, date.isoformat)
sqlite3.register_adapter(datetime, datetime.isoformat)
sqlite3.register_converter("date", lambda b: date.fromisoformat(b.decode()))
sqlite3.register_converter("datetime",
                           lambda b: datetime.fromisoformat(b.decode()))

con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)
con.execute("CREATE TABLE events(title TEXT, day DATE, starts DATETIME)")

con.execute("INSERT INTO events VALUES (?, ?, ?)",
            ("kickoff", date(2026, 11, 23), datetime(2026, 11, 23, 9, 0)))
con.execute("INSERT INTO events VALUES (?, ?, ?)",
            ("review", "2026-11-27", "2026-11-27 14:30:00"))

for title, day, starts in con.execute("SELECT * FROM events"):
    print(title, repr(day), repr(starts))

for row in con.execute("SELECT title, date(starts, '+7 days') FROM events"):
    print(row)
con.close()

The converter names date and datetime match the first word of the declared column types, regardless of case. A converter receives bytes, so it decodes them before parsing. The second row was inserted as plain strings, with a space instead of T; the converters handle it too, because fromisoformat accepts both separators. SQL expressions such as date(starts, '+7 days') have no declared type, so they come back as strings.

$ python converters.py
kickoff datetime.date(2026, 11, 23) datetime.datetime(2026, 11, 23, 9, 0)
review datetime.date(2026, 11, 27) datetime.datetime(2026, 11, 27, 14, 30)
('kickoff', '2026-11-30')
('review', '2026-12-04')

SQLite date functions

SQLite computes with dates in SQL. The functions date, time, datetime, julianday, unixepoch, and strftime take a value and a list of modifiers.

sql_functions.py
import sqlite3

con = sqlite3.connect(":memory:")

queries = [
    "date('2026-11-19', '+10 days')",
    "date('2026-11-19', 'start of month', '+1 month', '-1 day')",
    "date('2026-11-19', 'weekday 1')",
    "date('2026-01-31', '+1 month')",
    "date('2026-01-31', '+1 month', 'floor')",
    "julianday('2026-12-24') - julianday('2026-11-19')",
    "strftime('%Y-%m', '2026-11-19 14:30')",
    "strftime('%G-W%V', '2026-11-19')",
    "strftime('%w %j', '2026-11-19')",
    "datetime('2026-11-19T14:30:00+01:00')",
]

for sql in queries:
    value, = con.execute(f"SELECT {sql}").fetchone()
    print(f"{sql:60} {value}")
con.close()

Modifiers add intervals, jump to the start of a month, or move to the next given weekday, where 0 is Sunday. Adding a month to January 31 gives March 3 in SQLite, because February 31 overflows; the floor modifier, available since SQLite 3.46, clamps it to February 28 instead, as Python's relativedelta does. The difference of two Julian day numbers is the number of days between dates. Strings with offsets are converted to UTC.

$ python sql_functions.py
date('2026-11-19', '+10 days')                               2026-11-29
date('2026-11-19', 'start of month', '+1 month', '-1 day')   2026-11-30
date('2026-11-19', 'weekday 1')                              2026-11-23
date('2026-01-31', '+1 month')                               2026-03-03
date('2026-01-31', '+1 month', 'floor')                      2026-02-28
julianday('2026-12-24') - julianday('2026-11-19')            35.0
strftime('%Y-%m', '2026-11-19 14:30')                        2026-11
strftime('%G-W%V', '2026-11-19')                             2026-W47
strftime('%w %j', '2026-11-19')                              4 323
datetime('2026-11-19T14:30:00+01:00')                        2026-11-19 13:30:00

Mixed offsets sort wrong

Text columns compare as text. When values carry different UTC offsets or formats, the text order is not the time order.

mixed_offsets.py
import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE logins(user TEXT, at TEXT)")

rows = [
    ("alice", "2026-11-19T09:30:00+00:00"),
    ("bob", "2026-11-19T10:00:00+01:00"),
    ("carol", "2026-11-19T04:15:00-05:00"),
    ("dave", "2026-11-19 09:45:00"),
]
con.executemany("INSERT INTO logins VALUES (?, ?)", rows)

print("ORDER BY at:")
for user, at in con.execute("SELECT * FROM logins ORDER BY at"):
    print(f"  {user:6} {at}")

print("ORDER BY unixepoch(at):")
for user, at in con.execute("SELECT user, datetime(at) FROM logins "
                            "ORDER BY unixepoch(at)"):
    print(f"  {user:6} {at}")
con.close()

Sorted as text, Dave comes first because his value has a space where the others have T, and the offsets are ignored. Bob logged in at 10:00 in Bratislava, which is 9:00 UTC, so he was actually first. Sorting by unixepoch(at) converts every value to the same scale and gives the right order. Dave's value has no offset, so SQLite takes it as UTC, which may or may not be what the application meant.

$ python mixed_offsets.py
ORDER BY at:
  dave   2026-11-19 09:45:00
  carol  2026-11-19T04:15:00-05:00
  alice  2026-11-19T09:30:00+00:00
  bob    2026-11-19T10:00:00+01:00
ORDER BY unixepoch(at):
  bob    2026-11-19 09:00:00
  carol  2026-11-19 09:15:00
  alice  2026-11-19 09:30:00
  dave   2026-11-19 09:45:00

Storing UTC

The fix is to store every datetime in UTC in one format. We put the adapters and converters into a module, which also rejects naive datetimes.

dbdates.py
import sqlite3
from datetime import UTC, date, datetime


def adapt_datetime(dt):
    if dt.tzinfo is None:
        raise ValueError(f"naive datetime: {dt}")
    return dt.astimezone(UTC).isoformat()


sqlite3.register_adapter(date, date.isoformat)
sqlite3.register_adapter(datetime, adapt_datetime)
sqlite3.register_converter("date", lambda b: date.fromisoformat(b.decode()))
sqlite3.register_converter("datetime",
                           lambda b: datetime.fromisoformat(b.decode()))


def connect(path=":memory:"):
    types = sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES
    return sqlite3.connect(path, detect_types=types)

The datetime adapter converts aware values to UTC before formatting them and raises ValueError for naive ones. The connect function enables both type detection modes, so the following examples only import it.

utc_store.py
from datetime import datetime
from zoneinfo import ZoneInfo

from dbdates import connect

bratislava = ZoneInfo("Europe/Bratislava")
new_york = ZoneInfo("America/New_York")

con = connect()
con.execute("CREATE TABLE logins(user TEXT, at DATETIME)")
con.executemany("INSERT INTO logins VALUES (?, ?)", [
    ("bob", datetime(2026, 11, 19, 10, 0, tzinfo=bratislava)),
    ("carol", datetime(2026, 11, 19, 4, 15, tzinfo=new_york)),
])

for row in con.execute("SELECT user, CAST(at AS TEXT) FROM logins"):
    print(row)

for user, at in con.execute("SELECT * FROM logins ORDER BY at"):
    print(user, at.astimezone(bratislava), at.astimezone(new_york))

try:
    con.execute("INSERT INTO logins VALUES (?, ?)",
                ("dave", datetime(2026, 11, 19, 9, 45)))
except ValueError as e:
    print("ValueError:", e)
con.close()

Both logins are stored as UTC text with a +00:00 offset, so text order equals time order and ORDER BY at works. The converter returns aware datetimes in UTC, which we convert to any zone for display. The naive datetime is rejected before it reaches the database.

$ python utc_store.py
('bob', '2026-11-19T09:00:00+00:00')
('carol', '2026-11-19T09:15:00+00:00')
bob 2026-11-19 10:00:00+01:00 2026-11-19 04:00:00-05:00
carol 2026-11-19 10:15:00+01:00 2026-11-19 04:15:00-05:00
ValueError: naive datetime: 2026-11-19 09:45:00

Querying date ranges

Range queries compare text, so the parameters must use the same format as the stored values.

ranges.py
from datetime import date, datetime, timedelta
from zoneinfo import ZoneInfo

from dbdates import connect

local = ZoneInfo("Europe/Bratislava")

con = connect()
con.execute("CREATE TABLE orders(id INTEGER PRIMARY KEY, placed DATETIME)")
times = [datetime(2026, 11, 29, 22, 0, tzinfo=local),
         datetime(2026, 11, 30, 0, 30, tzinfo=local),
         datetime(2026, 11, 30, 18, 45, tzinfo=local),
         datetime(2026, 12, 1, 0, 0, tzinfo=local)]
con.executemany("INSERT INTO orders(placed) VALUES (?)", [(t,) for t in times])

sql = "SELECT id FROM orders WHERE placed BETWEEN ? AND ?"
print(con.execute(sql, ("2026-11-30", "2026-11-30")).fetchall())

start = datetime(2026, 11, 30, tzinfo=local)
stop = start + timedelta(days=1)
sql = "SELECT id, placed FROM orders WHERE placed >= ? AND placed < ?"
for id_, placed in con.execute(sql, (start, stop)):
    print(id_, placed.astimezone(local))

print(con.execute("SELECT id FROM orders WHERE date(placed) = ?",
                  (date(2026, 11, 30),)).fetchall())
con.close()

BETWEEN '2026-11-30' AND '2026-11-30' finds nothing: every stored value is longer than the bare date and so sorts after it. A half-open range from local midnight to the next midnight, passed as aware datetimes, goes through the same adapter and finds orders 2 and 3. Filtering with date(placed) is subtly wrong for local reports: it compares the UTC date, and so returns order 4, placed at midnight on December 1 in Bratislava, but misses order 2.

$ python ranges.py
[]
2 2026-11-30 00:30:00+01:00
3 2026-11-30 18:45:00+01:00
[(3,), (4,)]

Unix timestamp columns

An INTEGER column with Unix timestamps is compact, fast to compare, and free of format and offset problems.

unix_columns.py
import sqlite3
from datetime import UTC, datetime

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE readings(sensor TEXT, ts INTEGER, value REAL)")

start = int(datetime(2026, 11, 19, 8, 0, tzinfo=UTC).timestamp())
rows = [("s1", start + i * 900, 20.5 + i / 4) for i in range(4)]
con.executemany("INSERT INTO readings VALUES (?, ?, ?)", rows)

sql = """SELECT ts, datetime(ts, 'unixepoch'),
                datetime(ts, 'unixepoch', 'localtime'), value
         FROM readings"""
for row in con.execute(sql):
    print(row)

ts, = con.execute("SELECT max(ts) FROM readings").fetchone()
print(datetime.fromtimestamp(ts, UTC))

print(con.execute("SELECT unixepoch('2026-11-19 08:30:00')").fetchone())
print(con.execute("SELECT count(*) FROM readings WHERE ts >= ?",
                  (start + 1800,)).fetchone())
con.close()

The readings are stored as seconds since the epoch. The unixepoch modifier lets SQLite format them, and localtime converts the result to the local time zone. In Python, fromtimestamp with UTC converts a value back. The drawback is readability: in a database browser the column shows plain numbers.

$ python unix_columns.py
(1795075200, '2026-11-19 08:00:00', '2026-11-19 09:00:00', 20.5)
(1795076100, '2026-11-19 08:15:00', '2026-11-19 09:15:00', 20.75)
(1795077000, '2026-11-19 08:30:00', '2026-11-19 09:30:00', 21.0)
(1795077900, '2026-11-19 08:45:00', '2026-11-19 09:45:00', 21.25)
2026-11-19 08:45:00+00:00
(1795077000,)
(2,)

Grouping by month and week

The strftime function extracts parts of a date, which makes grouping in SQL easy.

by_month.py
import sqlite3
from datetime import date

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE sales(day TEXT, amount INTEGER)")

rows = []
for month in range(9, 13):
    for day in (3, 11, 19, 27):
        rows.append((date(2026, month, day).isoformat(), month * 10 + day))
con.executemany("INSERT INTO sales VALUES (?, ?)", rows)

sql = """SELECT strftime('%Y-%m', day) AS month, count(*), sum(amount)
         FROM sales GROUP BY month ORDER BY month"""
for row in con.execute(sql):
    print(row)

sql = """SELECT strftime('%G-W%V', day) AS week, sum(amount)
         FROM sales WHERE day >= ? AND day < ? GROUP BY week"""
print(con.execute(sql, ("2026-11-01", "2026-12-01")).fetchall())
con.close()

Formatting each day as %Y-%m groups the sales by month. The %G and %V codes give the ISO year and week. The range condition compares ISO dates as text, which is correct because all values have the same format.

$ python by_month.py
('2026-09', 4, 420)
('2026-10', 4, 460)
('2026-11', 4, 500)
('2026-12', 4, 540)
[('2026-W45', 113), ('2026-W46', 121), ('2026-W47', 129), ('2026-W48', 137)]

Types in column names

An aggregate such as max(at) has no declared type, so its result is a string. With PARSE_COLNAMES, the type can be given in the column alias.

colnames.py
from datetime import UTC, datetime

from dbdates import connect

con = connect()
con.execute("CREATE TABLE logins(user TEXT, at DATETIME)")
con.executemany("INSERT INTO logins VALUES (?, ?)", [
    ("alice", datetime(2026, 11, 18, 8, 5, tzinfo=UTC)),
    ("alice", datetime(2026, 11, 19, 9, 30, tzinfo=UTC)),
    ("bob", datetime(2026, 11, 19, 7, 50, tzinfo=UTC)),
])

sql = "SELECT user, max(at) FROM logins GROUP BY user"
print(con.execute(sql).fetchall())

sql = 'SELECT user, max(at) AS "last [datetime]" FROM logins GROUP BY user'
for user, last in con.execute(sql):
    print(user, repr(last))

sql = 'SELECT date(at) AS "day [date]", count(*) FROM logins GROUP BY 1'
print(con.execute(sql).fetchall())
con.close()

The first query returns strings. In the second, the alias "last [datetime]" selects the datetime converter, and the column name in the result is last. The alias of the third query applies the date converter to date(at). Because the full alias contains the type, the GROUP BY clause refers to the column by its position.

$ python colnames.py
[('alice', '2026-11-19T09:30:00+00:00'), ('bob', '2026-11-19T07:50:00+00:00')]
alice datetime.datetime(2026, 11, 19, 9, 30, tzinfo=datetime.timezone.utc)
bob datetime.datetime(2026, 11, 19, 7, 50, tzinfo=datetime.timezone.utc)
[(datetime.date(2026, 11, 18), 1), (datetime.date(2026, 11, 19), 2)]

Default timestamps

A column default fills in the time of insertion. SQLite's CURRENT_TIMESTAMP is UTC, but its format has no offset.

defaults.py
from datetime import UTC, datetime

from dbdates import connect

con = connect()
con.execute("""CREATE TABLE notes(
    body TEXT,
    created DATETIME DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
    stamp TEXT DEFAULT CURRENT_TIMESTAMP)""")

con.execute("INSERT INTO notes(body) VALUES ('buy milk')")

body, created, stamp = con.execute("SELECT * FROM notes").fetchone()
print(repr(created))
print(repr(stamp))

age = datetime.now(UTC) - created
print(age.total_seconds() < 1)

as_utc = datetime.fromisoformat(stamp).replace(tzinfo=UTC)
print(as_utc == created.replace(microsecond=0))
con.close()

The created column uses strftime to produce ISO text with a Z suffix, which the converter reads as an aware UTC datetime. The stamp column uses CURRENT_TIMESTAMP, which gives text with a space and no offset; we must attach UTC ourselves. Both values come from the same statement, so they agree to the second. The output depends on when the program runs.

$ python defaults.py
datetime.datetime(2026, 11, 19, 20, 40, 38, tzinfo=datetime.timezone.utc)
'2026-11-19 20:40:38'
True
True

Source

sqlite3 – Adapter and converter recipes

SQLite – Date and time functions

In this article we have stored dates in SQLite as ISO text and as Unix timestamps, replaced the deprecated default adapters, kept aware datetimes in UTC, and used SQLite's date functions for ranges and grouping. See also SQLite Python, sqlite3.register_adapter, sqlite3.register_converter, and Python datetime.

Author

I am Jan Bodnar, a programmer and technical writer. I have been writing programming tutorials at ZetCode since 2007 and have published eight e-books. See about the author.

List all Python tutorials.