SQL Unix Timestamp: MySQL, PostgreSQL & SQLite Epoch Queries

Current timestamp, per database

All three return whole seconds (a 10-digit value). For milliseconds you scale the fractional result yourself — none of them has a dedicated epoch-milliseconds function.

-- MySQL
SELECT UNIX_TIMESTAMP();          -- 1767225600 (seconds)
SELECT UNIX_TIMESTAMP(NOW(3)) * 1000;  -- milliseconds

-- PostgreSQL
SELECT EXTRACT(EPOCH FROM now());        -- 1767225600.123456 (float seconds)
SELECT (EXTRACT(EPOCH FROM now()) * 1000)::bigint;   -- milliseconds

-- SQLite
SELECT strftime('%s', 'now');           -- 1767225600 (seconds)
SELECT CAST(strftime('%s','now') AS INTEGER) * 1000 + CAST(substr(strftime('%f','now'),4,3) AS INTEGER); -- ms

Convert a timestamp to a date

FROM_UNIXTIME() and to_timestamp() are easy — but MySQL formats in the session timezone, while PostgreSQL and SQLite return timestamptz/UTC values. The same integer prints differently depending on SET time_zone in MySQL.

-- MySQL: output depends on the session timezone (default = server tz!)
SELECT FROM_UNIXTIME(1767225600);            -- 2026-01-01 00:00:00 in session tz
SET time_zone = '+00:00';
SELECT FROM_UNIXTIME(1767225600);            -- 2026-01-01 00:00:00 UTC
SELECT FROM_UNIXTIME(1767225600, '%Y-%m-%d %H:%i:%s');  -- custom format

-- PostgreSQL: to_timestamp() always returns timestamptz (UTC)
SELECT to_timestamp(1767225600);             -- 2026-01-01 00:00:00+00

-- SQLite: datetime() is UTC by default
SELECT datetime(1767225600, 'unixepoch');    -- 2026-01-01 00:00:00

Convert a date back to a timestamp

-- MySQL: interprets the literal in the SESSION timezone
SELECT UNIX_TIMESTAMP('2026-01-01 00:00:00');        -- session-tz dependent
SELECT UNIX_TIMESTAMP('2026-01-01 00:00:00 UTC');    -- explicit — deterministic
SELECT TIMESTAMPDIFF(SECOND, '1970-01-01', '2026-01-01');

-- PostgreSQL: make the zone explicit with AT TIME ZONE
SELECT EXTRACT(EPOCH FROM '2026-01-01 00:00:00'::timestamp AT TIME ZONE 'UTC');  -- 1767225600
SELECT EXTRACT(EPOCH FROM TIMESTAMPTZ '2026-01-01 00:00:00+00');

-- SQLite: strftime with 'unixepoch' modifier
SELECT strftime('%s', '2026-01-01 00:00:00');        -- 1767225600 (UTC)

Naive vs aware: the column-type story

• MySQL DATETIME — naive: a wall-clock string, no zone. TIMESTAMP columns convert to/from the session zone on write/read.
• PostgreSQL timestamp — naive; timestamptz — aware (stored UTC, displayed in the session zone).
• SQLite — stores whatever text you give it; the 'unixepoch' modifier is the only thing that makes epoch math safe.
• Rule: store timestamptz/UTC, and always pass an explicit zone when converting literals — never the session default.

Native precision: seconds

SQL epochs are seconds. MySQL's NOW(3)/NOW(6) carry fractional seconds inside TIMESTAMP, PostgreSQL now() has microseconds, and SQLite strftime('%f') exposes them — but every epoch function truncates to whole seconds unless you multiply the fraction yourself, as the examples above do.

Common pitfalls

• Session timezone — the same FROM_UNIXTIME query returns different times on different connections.
• Integer columns — 2038 overflows 32-bit signed INT; use BIGINT for Unix time.
• Postgres EXTRACT — returns numeric; cast to bigint before comparing with integer columns.
• SQLite text dates — '2026-01-01 00:00:00' comparisons work lexicographically only if every row uses the same format.

Try it live

Decode any of these integers in the epoch converter, or copy a ready-made query from the SQL snippet tab on the homepage.