Dates in SQLite are stored as text or numbers, because there’s no date type, and a set of built-in date functions does the work. In this article I’ll walk you through storing dates and times and doing arithmetic on them.
TLDR
- SQLite has no DATE or DATETIME type. Store timestamps as UTC text in the form
YYYY-MM-DD HH:MM:SS, or as Unix seconds in an INTEGER column. - Get the current time with
datetime('now')orCURRENT_TIMESTAMP. Both are UTC. - Calculate with modifiers such as
'+7 days'and'start of month', and format withstrftime(). - Filter a date range with
>=the start and<the day after the end.
What are dates and times in SQLite?
In SQLite, a date or time is an ordinary value that the date functions know how to read. The docs: “SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values” (Date and Time Datatype). So a date is either ISO-8601 text or a number. The numbers are Julian day numbers (REAL) or Unix timestamps (INTEGER).
SQLite date and time function syntax
SQLite has seven date and time functions (Date And Time Functions). Each takes a time value, followed by optional modifiers:
date(time-value, modifier, modifier, ...)
time(time-value, modifier, modifier, ...)
datetime(time-value, modifier, modifier, ...)
julianday(time-value, modifier, modifier, ...)
unixepoch(time-value, modifier, modifier, ...)
strftime(format, time-value, modifier, modifier, ...)
timediff(time-value, time-value)The first five show the same moment in different forms. date() and time() return one part of it, datetime() the full timestamp, and the last two return numbers:
sqlite> SELECT date('2026-10-06 14:30:15') AS d, time('2026-10-06 14:30:15') AS t, datetime('2026-10-06 14:30:15') AS dt, unixepoch('2026-10-06 14:30:15') AS unix, julianday('2026-10-06 14:30:15') AS jd;
d t dt unix jd
---------- -------- ------------------- ---------- ----------------
2026-10-06 14:30:15 2026-10-06 14:30:15 1791297015 2461320.10434028How to store and query dates in SQLite
To show you how this works, I will use an orders table with fixed timestamps, so the results are the same every time you run them.
Step 1: Get the current date and time
'now' means the current date and time in UTC, returned as 19-character ISO text. The exact value changes on every run, so this shows its type and shape:
sqlite> SELECT typeof(datetime('now')) AS type, length(datetime('now')) AS len, datetime('now') LIKE '____-__-__ __:__:__' AS iso_shape;
type len iso_shape
---- --- ---------
text 19 1Step 2: Store dates in a table
ISO-8601 text sorts in time order, so plain string comparison works. CURRENT_TIMESTAMP as a default fills in the UTC time of the insert. Unix seconds in an INTEGER column convert back with the 'unixepoch' modifier:
sqlite> CREATE TABLE orders (id INTEGER PRIMARY KEY, placed_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, placed_unix INTEGER);
sqlite> INSERT INTO orders (placed_at, placed_unix) VALUES ('2026-10-06 09:15:00', unixepoch('2026-10-06 09:15:00')), ('2026-09-30 23:59:59', unixepoch('2026-09-30 23:59:59')), ('2026-10-01 00:00:00', unixepoch('2026-10-01 00:00:00'));
sqlite> SELECT id, placed_at, placed_unix, datetime(placed_unix, 'unixepoch') AS from_unix FROM orders ORDER BY placed_at;
id placed_at placed_unix from_unix
-- ------------------- ----------- -------------------
2 2026-09-30 23:59:59 1790812799 2026-09-30 23:59:59
3 2026-10-01 00:00:00 1790812800 2026-10-01 00:00:00
1 2026-10-06 09:15:00 1791278100 2026-10-06 09:15:00Step 3: Filter a date range
Use a half-open interval: >= the first day and < the day after the last. It catches every time on the last day without guessing at 23:59:59:
sqlite> SELECT id, placed_at FROM orders WHERE placed_at >= '2026-10-01' AND placed_at < '2026-11-01' ORDER BY placed_at;
id placed_at
-- -------------------
3 2026-10-01 00:00:00
1 2026-10-06 09:15:00Step 4: Add and subtract with modifiers
Modifiers after the time value shift it or snap it to a boundary (modifiers):
sqlite> SELECT date('2026-10-06', '+7 days') AS next_week, date('2026-10-06', 'start of month') AS month_start, date('2026-10-06', 'start of month', '+1 month', '-1 day') AS month_end, date('2026-10-06', 'weekday 1') AS next_monday;
next_week month_start month_end next_monday
---------- ----------- ---------- -----------
2026-10-13 2026-10-01 2026-10-31 2026-10-12Examples
Each example below uses the same orders table or fixed dates.
Format a date with strftime
sqlite> SELECT strftime('%Y-%m', '2026-10-06') AS year_month, strftime('%d/%m/%Y %H:%M', '2026-10-06 14:30:15') AS uk, strftime('%w', '2026-10-06') AS weekday_0_sunday, strftime('%j', '2026-10-06') AS day_of_year;
year_month uk weekday_0_sunday day_of_year
---------- ---------------- ---------------- -----------
2026-10 06/10/2026 14:30 2 279%w counts Sunday as 0. The full list of substitutions is in the strftime() reference.
Group rows by month
Grouping by a formatted date gives per-month totals:
sqlite> SELECT strftime('%Y-%m', placed_at) AS month, count(*) AS orders FROM orders GROUP BY month ORDER BY month;
month orders
------- ------
2026-09 1
2026-10 2Find the difference between two dates
Subtract julianday() values for days, or unixepoch() values for seconds. timediff() returns a human-readable span (timediff()):
sqlite> SELECT julianday('2026-12-25') - julianday('2026-10-06') AS days, unixepoch('2026-10-06 18:00') - unixepoch('2026-10-06 09:15') AS seconds, timediff('2026-12-25', '2026-10-06') AS timediff;
days seconds timediff
---- ------- ------------------------
80.0 31500 +0000-02-19 00:00:00.000Edge cases
Dates in SQLite are text and numbers underneath, so a few things behave differently from a database with a real date type.
Adding months to the 31st
Adding a month to 31 January is ambiguous. SQLite’s default, ceiling, overflows into the next month. floor “resolves ambiguities by resolving to the last day of the previous month” (ambiguous dates):
sqlite> SELECT date('2026-01-31', '+1 month') AS plain, date('2026-01-31', '+1 month', 'floor') AS floor;
plain floor
---------- ----------
2026-03-03 2026-02-28Invalid dates aren’t always rejected
The functions return NULL for text they can’t parse, and for month 13 or day 32. A day from 29 to 31 that doesn’t exist in that month isn’t rejected, though. It rolls forward into the next month, so 30 February becomes 2 March. Validate input before you store it:
sqlite> SELECT date('2026-02-30') AS feb_30, date('2026-02-31') AS feb_31, date('2026-04-31') AS apr_31, date('2026-01-32') AS jan_32, date('2026-13-01') AS month_13, date('not a date') AS garbage;
feb_30 feb_31 apr_31 jan_32 month_13 garbage
---------- ---------- ---------- ------ -------- -------
2026-03-02 2026-03-03 2026-05-01Local time depends on the machine
The localtime modifier “assumes the time-value to its left is in Universal Coordinated Time (UTC) and adjusts that time value so that it is in localtime” (localtime). The result depends on the machine’s time zone. I ran this with the time zone set to Asia/Kolkata (UTC+5:30):
sqlite> SELECT datetime('2026-10-06 09:15:00', 'localtime') AS local; -- run with TZ=Asia/Kolkata
local
-------------------
2026-10-06 14:45:00Store UTC and convert at display time. Mixing local and UTC values in one column breaks ranges and sorting.
Python’s datetime adapter is deprecated
Passing a Python datetime straight into a query still works, but Python 3.12 deprecated the adapter that does it. An aware datetime is also stored with a +00:00 suffix. Formatting the value yourself stores the plain form:
import sqlite3, warnings
from datetime import datetime, timezone
warnings.simplefilter("always") # show deprecation warnings
con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE orders (placed_at TEXT)")
when = datetime(2026, 10, 6, 9, 15, tzinfo=timezone.utc)
con.execute("INSERT INTO orders VALUES (?)", (when,)) # relies on the default adapter
con.execute("INSERT INTO orders VALUES (?)", (when.strftime("%Y-%m-%d %H:%M:%S"),)) # explicit text
for (value,) in con.execute("SELECT placed_at FROM orders"):
print(repr(value))
con.close()$ python3 dt_python.py
dt_python.py:9: DeprecationWarning: The default datetime adapter is deprecated as of Python 3.12; see the sqlite3 documentation for suggested replacement recipes
con.execute("INSERT INTO orders VALUES (?)", (when,)) # relies on the default adapter
'2026-10-06 09:15:00+00:00'
'2026-10-06 09:15:00'Conclusion
Store dates in SQLite as UTC text in YYYY-MM-DD HH:MM:SS form, or as Unix seconds. Use datetime('now') for the current time. Calculate with modifiers, and format with strftime(). Validate dates before storing them, and convert to local time only when you display them.
Further reading
- SQLite data types
- Dates in SQLite vs MySQL
- Python SQLite3 tutorial
- SQLite docs: Date And Time Functions
Tested with SQLite 3.51.0 and Python 3.13.14 on 7 October 2026.