Unix timestamps in SQLite
SQLite has no date type. You store Unix integers or ISO text, and its date functions convert between them.
Get the current Unix timestamp
SELECT unixepoch(); -- seconds (or strftime('%s', 'now') on older versions)
SELECT unixepoch('subsec') * 1000; -- milliseconds, with the subsec modifier
Convert a Unix timestamp to a date
SELECT datetime(1700000000, 'unixepoch'); -- 2023-11-14 22:13:20
SELECT datetime(1700000000123 / 1000.0, 'unixepoch', 'subsec');
Convert a date to a Unix timestamp
SELECT unixepoch('2026-11-14 10:00:00'); -- 1794650400
SELECT unixepoch('2026-11-14T15:30:00+05:30'); -- 1794650400
Timezones
SELECT datetime(1700000000, 'unixepoch', 'localtime'); -- the machine's local zone
SQLite knows only UTC and the machine's local zone. Convert to other zones in your application.
Common mistakes
- Text times without an offset are read as UTC.
- The
automodifier guesses between Julian day numbers and Unix times by size. Unix times below 5,373,484.5 (about the first 62 days of 1970) are read as Julian days. - Store one format per column. Mixing integers and text makes sorting and comparisons wrong.
Complete example
SELECT
unixepoch() AS now_s,
datetime(1700000000, 'unixepoch') AS utc,
unixepoch(datetime(1700000000, 'unixepoch')) AS back;
Check any value with the epoch converter, or convert a whole column with the batch converter. All examples use 1700000000 (2023-11-14 22:13:20 UTC) and 1794650400 (2026-11-14 10:00:00 UTC).