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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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
List all Python tutorials.