Unix timestamps in SQL.

Get current Unix timestamp

Each database has its own syntax — these are the common ways.

PostgreSQL

-- seconds (integer)
SELECT EXTRACT(EPOCH FROM NOW())::bigint;

-- milliseconds
SELECT (EXTRACT(EPOCH FROM NOW()) * 1000)::bigint;

MySQL

-- seconds
SELECT UNIX_TIMESTAMP();

-- or for a specific column
SELECT UNIX_TIMESTAMP(NOW());

SQLite

-- seconds (since 3.38)
SELECT unixepoch();

-- works on older versions too:
SELECT strftime('%s', 'now');

SQL Server

-- seconds since 1970
SELECT DATEDIFF(SECOND, '1970-01-01', GETUTCDATE());

Convert epoch to timestamp

Going from an integer seconds value to a native timestamp column.

PostgreSQL

-- seconds
SELECT TO_TIMESTAMP(1735689600);
-- → 2025-01-01 00:00:00+00

-- with timezone conversion
SELECT TO_TIMESTAMP(1735689600) AT TIME ZONE 'America/New_York';

MySQL

-- seconds
SELECT FROM_UNIXTIME(1735689600);
-- → '2025-01-01 00:00:00' (in server's timezone)

-- with format
SELECT FROM_UNIXTIME(1735689600, '%Y-%m-%dT%H:%i:%sZ');

SQLite

SELECT datetime(1735689600, 'unixepoch');
-- → '2025-01-01 00:00:00' (UTC)

-- with local time
SELECT datetime(1735689600, 'unixepoch', 'localtime');

SQL Server

SELECT DATEADD(SECOND, 1735689600, '1970-01-01');
-- → 2025-01-01 00:00:00.000

Convert timestamp to epoch

Going from a native timestamp column back to an integer.

PostgreSQL

SELECT EXTRACT(EPOCH FROM TIMESTAMP '2025-01-01 00:00:00')::bigint;
-- → 1735689600

-- from a column
SELECT EXTRACT(EPOCH FROM created_at)::bigint FROM events;

MySQL

SELECT UNIX_TIMESTAMP('2025-01-01 00:00:00');
-- → 1735689600 (assumes server timezone!)

-- explicit UTC
SELECT UNIX_TIMESTAMP(CONVERT_TZ('2025-01-01 00:00:00', '+00:00', @@session.time_zone));

SQLite

SELECT strftime('%s', '2025-01-01 00:00:00');
-- → '1735689600' (returned as text)

-- as integer
SELECT unixepoch('2025-01-01 00:00:00');

SQL Server

SELECT DATEDIFF(SECOND, '1970-01-01', '2025-01-01 00:00:00');
-- → 1735689600

Milliseconds

If you need millisecond precision, here's how to handle it.

PostgreSQL

-- get current ms
SELECT (EXTRACT(EPOCH FROM NOW()) * 1000)::bigint;

-- ms → timestamp
SELECT TO_TIMESTAMP(1735689600000 / 1000.0);

MySQL

-- MySQL stores microsecond precision since 5.6
SELECT UNIX_TIMESTAMP(NOW(3)) * 1000;  -- with millisecond fractional

SQLite

SELECT unixepoch('now', 'subsec') * 1000;  -- 3.42+

SQL Server

SELECT DATEDIFF_BIG(MILLISECOND, '1970-01-01', SYSUTCDATETIME());

Common gotchas

SQL date pitfalls that vary by database.

Timezone handling differs wildly

-- PostgreSQL has TIMESTAMP WITH TIME ZONE — use it.
-- MySQL's TIMESTAMP column converts to UTC on storage,
-- back to session timezone on retrieval. DATETIME does NOT.
-- SQLite has no timezone type — values are just text/integers.
-- SQL Server has DATETIMEOFFSET for timezone-aware values.

Integer overflow on Y2038

-- A column typed as INTEGER (32-bit) overflows at the Y2038 boundary.
-- Use BIGINT for any timestamp column.
CREATE TABLE events (ts BIGINT);  -- ✓
CREATE TABLE events (ts INTEGER); -- ✗ on 32-bit systems

Date math precedence

-- Always cast or be explicit about whether you want
-- integer (truncation) or float math.
-- This is integer division in PostgreSQL:
SELECT 1735689600000 / 1000;  -- gives 1735689600
-- This is float:
SELECT 1735689600000 / 1000.0;

Use the converter

Need to quickly check what a specific timestamp converts to? Use the main converter — paste any value and get every format back.

Working with many timestamps at once? Try the batch converter — paste a list, get a CSV or JSON file back.