SQL epoch & Unix timestamp converter

A Unix timestamp — what most database docs call SQL epoch time — is the number of seconds since 1970-01-01T00:00:00Z, and every major SQL engine ships built-in functions to convert it to a readable datetime and back again. Converting a unix timestamp in SQL comes up constantly: normalizing an API's epoch column into a human-readable date, computing a datetime to epoch difference for a TTL or expiry check, or joining rows from services that store time in different units. The three most common engines disagree on the function name, and on what unit they expect, so a query that runs cleanly in MySQL throws in PostgreSQL and silently returns the wrong century in SQLite. MySQL exposes FROM_UNIXTIME() and UNIX_TIMESTAMP(); PostgreSQL uses to_timestamp() and EXTRACT(EPOCH FROM …); SQLite treats time as a modifier passed to a small family of date and time functions rather than a dedicated pair. This page covers the exact syntax for all three, the pitfalls each one provokes — the millisecond-versus-second mismatch chief among them — and a working query for every direction you will need: epoch to datetime, and datetime back to epoch.

MySQL: FROM_UNIXTIME() and UNIX_TIMESTAMP()

FROM_UNIXTIME() takes epoch seconds and returns a DATETIME; UNIX_TIMESTAMP() does the reverse, and called with no argument returns the current epoch.

-- Epoch → datetime
SELECT FROM_UNIXTIME(1786795200);

-- Datetime → epoch
SELECT UNIX_TIMESTAMP('2026-08-15 12:00:00');

-- Format the result directly
SELECT FROM_UNIXTIME(1786795200, '%Y-%m-%d %H:%i:%s');

-- Current epoch
SELECT UNIX_TIMESTAMP();

PostgreSQL: to_timestamp() and EXTRACT(EPOCH FROM …)

to_timestamp() converts epoch seconds to a timestamptz; EXTRACT(EPOCH FROM …) pulls the epoch back out of any timestamp expression, including a table column.

-- Epoch → datetime
SELECT to_timestamp(1786795200);

-- Datetime → epoch
SELECT EXTRACT(EPOCH FROM TIMESTAMP '2026-08-15 12:00:00');

-- Against a table column
SELECT to_timestamp(created_at) FROM events;

-- Current epoch
SELECT EXTRACT(EPOCH FROM now());

SQLite: datetime() and strftime('%s', …)

SQLite has no dedicated epoch functions — instead, 'unixepoch' is a modifier you pass to datetime(), and strftime('%s', …) with the %s format code returns the epoch as text.

-- Epoch → datetime
SELECT datetime(1786795200, 'unixepoch');

-- Datetime → epoch
SELECT strftime('%s', '2026-08-15 12:00:00');

-- Current epoch
SELECT strftime('%s', 'now');

Those three cover most application databases; the engines a data team reaches for next spell the same two conversions differently again, and the spelling is the whole difficulty. SQL Server has no epoch function at all and does the arithmetic with date maths, while BigQuery and Snowflake each ship a dedicated pair:

Two footnotes that bite. DATEDIFF(s, …) returns a 32-bit INT in SQL Server, so it overflows for any instant past 2038-01-19 — use DATEDIFF_BIG there. And Snowflake's TO_TIMESTAMP() guesses the unit from the magnitude of the number rather than erroring on a millisecond column, which is the millisecond trap below wearing a helpful face.

Every function above returns an instant, not a rendering. PostgreSQL and SQL Server both spell the rendering step AT TIME ZONE, and it is a separate step on purpose: the stored epoch has no timezone, so the report's timezone is a choice made at read time, once per report rather than once per row.

-- Postgres: the same instant, rendered for a Mumbai report
SELECT to_timestamp(created_at) AT TIME ZONE 'Asia/Kolkata' FROM events;

-- SQL Server: tag the naive column UTC first, then render
SELECT DATEADD(s, created_at, '1970-01-01') AT TIME ZONE 'UTC'
         AT TIME ZONE 'India Standard Time';

Both spellings take a zone name rather than an offset, and that is deliberate: 'Asia/Kolkata' and 'India Standard Time' carry the whole history of that region's rules, so the same query renders correctly on either side of a clock change. Hard-coding +05:30 or -05:00 instead is the bug that surfaces twice a year — see how a named zone resolves an epoch, and where DST moves the offset for the rendering rules the database is applying here.

The millisecond trap

Every function above expects seconds, but plenty of columns hold milliseconds — usually because they were written by JavaScript's millisecond Date.now() or Java's System.currentTimeMillis(). Handed a 13-digit millisecond value unmodified, to_timestamp() and FROM_UNIXTIME() do not error — they return a date thousands of years in the future, because the function has no way to know the unit changed. Divide by 1000 first:

-- Postgres, given a millisecond column
SELECT to_timestamp(created_at_ms / 1000.0);

-- MySQL, same fix
SELECT FROM_UNIXTIME(created_at_ms / 1000);

-- SQLite
SELECT datetime(created_at_ms / 1000, 'unixepoch');

Not sure which unit a value is in? The digit-count check settles it in one look — ten digits is seconds, thirteen is milliseconds.

Frequently asked questions

How do I convert a Unix timestamp to a date in SQL?

Use FROM_UNIXTIME() in MySQL, to_timestamp() in PostgreSQL, or datetime(value, 'unixepoch') in SQLite. Each takes epoch seconds and returns a native date or timestamp value.

What is SQL epoch time?

SQL epoch time, or Unix time, is the count of seconds since 1970-01-01T00:00:00 UTC. It is the same epoch JavaScript and Java use, just expressed in seconds rather than milliseconds.

How do I convert a datetime to epoch in SQL?

Use UNIX_TIMESTAMP() in MySQL, EXTRACT(EPOCH FROM …) in PostgreSQL, or strftime('%s', …) in SQLite, passing the datetime value or column.

Why does my epoch value look 1000x too large in SQL?

You likely have a millisecond timestamp (13 digits) rather than seconds (10 digits). SQL epoch functions expect seconds, so divide by 1000 first or the resulting date will land centuries in the future.

How do I get the current Unix timestamp in SQL?

SELECT UNIX_TIMESTAMP() in MySQL, SELECT EXTRACT(EPOCH FROM now()) in PostgreSQL, or SELECT strftime('%s','now') in SQLite.

Where to go next

Enter the same value on the main converter to see it in seven formats at once, or check whether a bare number is seconds or milliseconds before it goes anywhere near a query. When the query result is headed for a spreadsheet rather than a report, the Excel serial-date formulas convert the epoch column after export — Excel counts days from 1900, not seconds from 1970, so a raw epoch pasted into a cell reads as gibberish without them.