MySQL & PostgreSQL

Convert a Unix timestamp in SQL

Both MySQL and PostgreSQL have first-class functions for epoch conversion. `UNIX_TIMESTAMP()` / `extract(epoch from now())` read the current time; the reverse direction formats a timestamp for display.

Remember that SQL timestamps depend on the session timezone: `FROM_UNIXTIME()` returns session-local time, so pair it with `CONVERT_TZ()` or `UTC_TIMESTAMP()` when you need UTC.

MySQL: current Unix timestamp (seconds)

SELECT UNIX_TIMESTAMP();
-- 1,754,136,000

MySQL: epoch seconds → datetime

SELECT FROM_UNIXTIME(1754136000);                           -- server tz
SELECT FROM_UNIXTIME(1754136000, '%Y-%m-%d %H:%i:%s');  -- formatted

PostgreSQL: current Unix timestamp (seconds)

SELECT extract(epoch FROM now())::int;
-- 1,754,136,000

PostgreSQL: epoch seconds ↔ timestamptz

SELECT to_timestamp(1754136000);                       -- epoch → timestamptz
SELECT extract(epoch FROM timestamp '2025-08-02 12:00:00'); -- ISO → epoch

Real-world use cases for SQL

Partitioning event tables by day using extract(epoch FROM created_at) for fast range scans.
Syncing a mobile client clock by returning SELECT UNIX_TIMESTAMP() from a health-check endpoint.
Converting legacy integer timestamp columns to timestamptz with to_timestamp() during a migration.

Unix timestamp (seconds)	ISO 8601 (UTC)
0	1970-01-01T00:00:00.000Z
946,684,800	2000-01-01T00:00:00.000Z
1,754,136,000	2025-08-02T12:00:00.000Z
2,147,483,647	2038-01-19T03:14:07.000Z
2,524,608,000	2050-01-01T00:00:00.000Z

These are the same fixed timestamps used in the SQL examples above — every conversion returns one of these values.

Frequently asked questions

How do I get the current Unix timestamp in MySQL?

SELECT UNIX_TIMESTAMP() returns the current epoch in seconds. For a timestamp column, UNIX_TIMESTAMP(created_at) converts it. Store timestamps as BIGINT when you need to stay timezone-independent.

How do I get the current Unix timestamp in PostgreSQL?

SELECT extract(epoch FROM now())::int returns the current epoch in seconds. The ::int cast truncates the fractional part; drop it if you want the full precision float.

How do I convert an epoch timestamp to a date in SQL?

MySQL: SELECT FROM_UNIXTIME(1754136000). PostgreSQL: SELECT to_timestamp(1754136000). Both render in the session timezone, so pair them with CONVERT_TZ() or an explicit timestamptz when you need UTC.

Epoch to human date in SQL

Try it live — convert any timestamp in SQL-friendly format.

Enter Unix Timestamp

Seconds or milliseconds — the unit is auto-detected. Example: 1735689600

Waiting for input…

Timezone

UTC ISO 8601

–

Local — auto

–

RFC 2822 / GMT

–

Seconds value

–

Relative time

–

Other programming languages

Copy-paste epoch conversion snippets in your favorite stack

View all code snippets →

More timestamp guides

Example values used above: epoch seconds = 1,754,136,000 · ISO 8601 = 2025-08-02T12:00:00.000Z. Curious how the numbers work? See the Unix epoch time FAQ, or view the current epoch time in any timezone.