Convert Unix Timestamp in SQLite

SQLite provides a set of powerful date and time functions. The most common for Unix timestamp conversions are datetime() and strftime(), often used with the 'unixepoch' modifier.

Unix Timestamp to Date/Time

SQLite

-- Basic conversion (seconds to YYYY-MM-DD HH:MM:SS)
SELECT datetime(1704067200, 'unixepoch');
-- Result: 2024-01-01 00:00:00

-- To local time
SELECT datetime(1704067200, 'unixepoch', 'localtime');
-- Result depends on server timezone, e.g., 2023-12-31 19:00:00

-- Custom formatting using strftime
-- %Y-year, %m-month, %d-day, %H-hour, %M-minute, %S-second
SELECT strftime('%Y-%m-%d', 1704067200, 'unixepoch');
-- Result: 2024-01-01

-- From milliseconds (math required)
SELECT datetime(1704067200000 / 1000, 'unixepoch');
-- Result: 2024-01-01 00:00:00

Date/Time to Unix Timestamp

SQLite

-- Current time as Unix timestamp (seconds)
SELECT strftime('%s', 'now');
-- Result: 1704067200 (changes every second)

-- From a specific ISO8601 string
SELECT strftime('%s', '2024-01-01 00:00:00');
-- Result: 1704067200

-- From 'now' plus modifiers
SELECT strftime('%s', 'now', '+1 day');
-- Result: Timestamp for tomorrow same time

-- From 'now' start of month
SELECT strftime('%s', 'now', 'start of month');
-- Result: Timestamp for 1st of current month at 00:00:00

Using Julianday for Calculations

For complex date math, julianday() is useful as it returns the day number as a continuous count. You can convert between Julian days and Unix timestamps.

SQLite

-- Difference in days between two timestamps
SELECT julianday(datetime(1706745600, 'unixepoch')) 
     - julianday(datetime(1704067200, 'unixepoch'));
-- Result: 31.0

-- Add days to a timestamp
SELECT strftime('%s', datetime(1704067200, 'unixepoch', '+7 days'));
-- Result: 1704672000

Storage Best Practices

SQLite Schema

CREATE TABLE user_logs (
    id INTEGER PRIMARY KEY,
    -- Store as INTEGER for efficiency and simple sorting
    login_time_unix INTEGER, 
    -- Or Store as TEXT (ISO8601) for readability
    login_time_iso TEXT
);

INSERT INTO user_logs (login_time_unix, login_time_iso) 
VALUES (strftime('%s','now'), datetime('now'));

-- Querying efficiently
SELECT * FROM user_logs WHERE login_time_unix > 1704067200;

Common Pitfalls

Milliseconds confusion

The 'unixepoch' modifier strictly interprets the input as seconds. Passing milliseconds (e.g., from JavaScript Date.now()) will result in a date thousands of years in the future.

Localtime vs UTC

SQLite defaults to UTC. Using 'localtime' converts the UTC time to the server/client local time. Be careful when round-tripping data; usually, it's safer to store and work in UTC and only convert to localtime for display.

-- Dangerous if server TZ changes
SELECT datetime(1704067200, 'unixepoch', 'localtime');

Frequently Asked Questions

How do I convert a Unix timestamp to datetime in SQLite?

Use datetime(timestamp, 'unixepoch'). For example: SELECT datetime(1704067200, 'unixepoch');

How do I get the current Unix timestamp in SQLite?

Use strftime('%s', 'now') to get the current time as seconds since 1970-01-01.

Does SQLite store dates as a specific data type?

No. SQLite uses "affinity" storage. Dates are typically stored as TEXT (ISO8601), REAL (Julian days), or INTEGER (Unix timestamps).

How do I handle milliseconds in SQLite?

SQLite's unixepoch modifier expects seconds. If you have milliseconds, divide by 1000. For outputting fractional seconds, use strftime('%f', ...).