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
SQLite SQL
SELECT datetime(1679616000, 'unixepoch');SELECT datetime(1679616000, 'unixepoch', 'localtime');SELECT strftime('%Y-%m-%d %H:%M:%f', 1679616000000 / 1000.0, 'unixepoch');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
| Event | Epoch (seconds) | Date |
|---|---|---|
| Unix Epoch | 0 | January 1, 1970 00:00:00 UTC |
| 1 Billion | 1000000000 | September 9, 2001 01:46:40 UTC |
| Y2K38 (32-bit limit) | 2147483647 | January 19, 2038 03:14:07 UTC |
| 2 Billion | 2000000000 | May 18, 2033 03:33:20 UTC |
| Current (approx) | ~1774000000 | March 2026 |
Frequently asked questions
Does SQLite have a timestamp type?
How should I store a timestamp in SQLite?
Why is SQLite CURRENT_TIMESTAMP the wrong time?
Why does SQLite say "no such function: unixepoch"?
How do I get a SQLite timestamp with milliseconds?
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