SQLite Timestamp Converter
SQLite does not have a dedicated timestamp or datetime column type. Instead, it stores dates and times using one of three storage classes: TEXT (as ISO 8601 strings), INTEGER (as Unix timestamps), or REAL (as Julian day numbers). Of these, storing timestamps as Unix INTEGER values is the most performant and the recommended approach for new applications.
SQLite's date and time functions work correctly with all three storage formats. The key functions are datetime() for formatting timestamps as strings, strftime() for custom formatting, unixepoch() (SQLite 3.38.0+) for getting Unix timestamps, and julianday() for Julian day calculations. All of these functions accept the special string 'now' to represent the current moment.
SQLite timestamp examples
Current Unix timestamp (modern SQLite 3.38+):
SELECT unixepoch('now');
Current Unix timestamp (all versions):
SELECT strftime('%s', 'now');
Unix integer to readable date:
SELECT datetime(1700000000, 'unixepoch');
→ '2023-11-14 22:13:20'
In local time:
SELECT datetime(1700000000, 'unixepoch', 'localtime');
Date string to Unix:
SELECT unixepoch('2026-04-17 10:35:00');
Filter records from the last hour:
SELECT * FROM events WHERE ts > unixepoch('now') - 3600;
Store current time on insert:
CREATE TABLE events (id INTEGER PRIMARY KEY, ts INTEGER DEFAULT (unixepoch('now')));