SQLite Timestamp Converter

Convert Unix epoch timestamps to SQLite TEXT datetimes and Julian day numbers and back, then copy the datetime() and unixepoch() queries for your value.

Client-side only, nothing leaves your browser

Auto-detected: seconds  ·  3.7y ago

Conversions

Epoch (seconds)
1679616000
Epoch (milliseconds)
1679616000000
Human Readable
Friday, March 24, 2023 at 04:00:00 AM GMT+4
ISO 8601
2023-03-24T00:00:00.000Z
SQLite TEXT (UTC)
2023-03-24 00:00:00
Julian day (REAL)
2460027.500000

SQLite SQL

Epoch to TEXT datetime (UTC)
SELECT datetime(1679616000, 'unixepoch');
Epoch to local time
SELECT datetime(1679616000, 'unixepoch', 'localtime');
Epoch milliseconds to datetime
SELECT strftime('%Y-%m-%d %H:%M:%f', 1679616000000 / 1000.0, 'unixepoch');
Epoch to Julian day
SELECT julianday(1679616000, 'unixepoch');

SQLite Timestamps & Storage

SQLite has no date or time storage class. Its date functions read and write three forms: TEXT in ISO-8601 (YYYY-MM-DD HH:MM:SS), INTEGER Unix epoch seconds, and REAL Julian day numbers. A column declared TIMESTAMP or DATETIME gets NUMERIC affinity and keeps whichever form you insert. A bare number passed to datetime() is read as a Julian day, so add the 'unixepoch' modifier when the number is an epoch.

Quick Reference

  • datetime(epoch, 'unixepoch'): Epoch seconds to a TEXT datetime in UTC
  • unixepoch(text): Date to epoch seconds as an INTEGER (SQLite 3.38.0+)
  • strftime('%s', text): Date to epoch seconds as TEXT (any version)
  • julianday(text): Date to a REAL Julian day number
  • 'localtime' modifier: Shift a UTC value to the machine's local time

Notable Epoch Values

EventEpoch (seconds)Date
Unix Epoch0January 1, 1970 00:00:00 UTC
1 Billion1000000000September 9, 2001 01:46:40 UTC
Y2K38 (32-bit limit)2147483647January 19, 2038 03:14:07 UTC
2 Billion2000000000May 18, 2033 03:33:20 UTC
Current (approx)~1774000000March 2026

Frequently asked questions

Does SQLite have a timestamp type?
SQLite accepts TIMESTAMP or DATETIME as a column type, but there is no real date type behind the name. The column gets NUMERIC affinity, so an ISO-8601 string stays TEXT and an epoch number stays INTEGER. SQLite's date functions understand TEXT, INTEGER Unix time and REAL Julian day values, and you decide which one the column holds.
How should I store a timestamp in SQLite?
Pick one format per column and stick to it, because SQLite compares stored values, not dates. INTEGER Unix seconds are compact and fast to compare, and unixepoch('now') fills them. TEXT in 'YYYY-MM-DD HH:MM:SS' is readable in any GUI and still sorts correctly, and it is what DEFAULT CURRENT_TIMESTAMP writes. Store UTC either way and convert to local time when you read.
Why is SQLite CURRENT_TIMESTAMP the wrong time?
CURRENT_TIMESTAMP is always UTC, so it looks hours off if you expected local time. Convert when you read it with datetime(created_at, 'localtime'), or write local time directly with datetime('now', 'localtime'). The 'localtime' modifier uses the time zone of the machine running SQLite, so the same query can return different results on your laptop and on a server.
Why does SQLite say "no such function: unixepoch"?
unixepoch() was added in SQLite 3.38.0, released in February 2022, and many bundled copies of SQLite are older. Check with SELECT sqlite_version(). On older versions, CAST(strftime('%s', 'now') AS INTEGER) returns the same number, since strftime('%s') gives the epoch as text.
How do I get a SQLite timestamp with milliseconds?
CURRENT_TIMESTAMP only goes down to whole seconds. For text, use strftime('%Y-%m-%d %H:%M:%f', 'now'), where %f gives seconds with three decimals. For an integer millisecond epoch on SQLite 3.42.0 or later, use CAST(unixepoch('now', 'subsec') * 1000 AS INTEGER). To read a millisecond value back, divide by 1000.0 first, because datetime(1711238400123, 'unixepoch') returns NULL.
SQLite

Need a SQLite GUI Client?

1bench is a modern database client for SQLite and 20+ other databases. Query, browse, and manage your data visually.

Learn More