Postgres extract(epoch) vs MySQL UNIX_TIMESTAMP(): What You Get Back

Both databases can hand you a Unix timestamp, but they disagree on the details. Run extract(epoch from now()) in Postgres and UNIX_TIMESTAMP() in MySQL against the same instant and you get the same number of seconds — yet different data types, different precision, and different timezone assumptions. Those gaps cause off-by-1000 bugs and silent float rounding when you move values between systems. Here is exactly what each returns and how to keep them aligned.

#TL;DR

• extract(epoch from now()) returns seconds since 1970 UTC, with microsecond fractions.
• In Postgres 14+ that result is numeric; in 13 and earlier it is double precision.
• MySQL UNIX_TIMESTAMP() returns an integer, or DECIMAL when the input has fractional seconds.
• Both count from 1970-01-01 00:00:00 UTC, so the same instant gives the same seconds.
• Store bigint seconds for portability; cast away floats before they bite you.

#What does extract(epoch from now()) return in Postgres?

In Postgres, now() returns a value of type timestamp with time zone (often written timestamptz). Wrapping it in extract(epoch from ...) gives you the number of seconds elapsed since the Unix epoch, 1970-01-01 00:00:00 UTC. The result is a high-resolution number, not a rounded integer.

SELECT extract(epoch from now());
-- 1749254400.123456

Per the PostgreSQL date/time functions documentation, for timestamp with time zone the epoch field is "the number of seconds since 1970-01-01 00:00:00 UTC," and it carries the same sub-second precision as the source timestamp — microseconds.

#Why extract(epoch) returns numeric in Postgres 14 and later

This is the detail that trips up code written against older servers. Before version 14, extract() returned double precision (float8). Postgres 14 changed the return type to numeric to avoid loss-of-precision issues in some calculations. If you need the old float behavior, the underlying date_part() function still returns double precision.

-- Postgres 14+: returns numeric
SELECT extract(epoch from now());

-- Always double precision, any version
SELECT date_part('epoch', now());

The practical consequence: a client driver that maps numeric to a decimal type and double precision to a float will deserialize the same query into different language types depending on the server version. Pin the type yourself with a cast if your application cares.

#How timestamp without time zone changes the answer

If you extract epoch from a timestamp without time zone, Postgres returns the nominal seconds since 1970-01-01 00:00:00, treating the stored value as-is with no timezone conversion. That means two columns holding "the same wall-clock time" but conceptually in different zones produce identical epoch numbers — which is almost never what you want.

• timestamptz → epoch measured against UTC, the correct absolute instant.
• timestamp (no zone) → epoch measured against the literal value, zone-agnostic.

The fix is to use timestamptz for anything that represents a real moment in time, and reserve naked timestamp for things like "9:00 AM in whatever local zone the user is in."

#What does UNIX_TIMESTAMP() return in MySQL?

Called with no argument, MySQL's UNIX_TIMESTAMP() returns the seconds since the epoch as an integer:

SELECT UNIX_TIMESTAMP();
-- 1749254400

According to the MySQL date and time functions reference, the return value is an integer when no argument is given or the argument has no fractional-seconds part, and a DECIMAL when the argument includes fractional seconds. So unlike Postgres, MySQL hands you a clean integer by default — no float, no decimal point.

#Why UNIX_TIMESTAMP() returns DECIMAL with fractional seconds

To get sub-second resolution you must pass a value that carries it, typically NOW(6), which produces a DATETIME with six fractional digits:

SELECT UNIX_TIMESTAMP(NOW(6));
-- 1749254400.123456

Here the result type is DECIMAL, and the number of fractional digits in the output matches the precision of the input. UNIX_TIMESTAMP(NOW(3)) yields three decimal places. This is the inverse situation from Postgres: MySQL defaults to integer and opts into fractions, while Postgres defaults to fractional precision and you cast down to an integer.

#How MySQL handles the session time zone

The MySQL docs note that UNIX_TIMESTAMP() "assumes that its argument is a datetime value in the session time zone." So if you pass a string or DATETIME literal, MySQL interprets it using the connection's time_zone setting before computing the epoch. Change the session zone and the same literal maps to a different timestamp. With no argument it uses the current instant, so there is no ambiguity — but the moment you feed it a literal, the session zone matters.

#How to get a Unix timestamp in milliseconds in Postgres and MySQL

Neither default gives you milliseconds, which is what most JavaScript and Java code expects. The conversion is a multiply, but watch the type.

#Multiplying seconds to milliseconds safely

A frequent bug: code reads Postgres seconds (with a .123456 fraction) and treats it as if it were MySQL integer seconds, or multiplies one side by 1000 and not the other. If your backend stores milliseconds but your database returns seconds, every timestamp lands in 1970 or far in the future. When a stray value looks wrong, paste it into the Unix timestamp converter to see instantly whether it is seconds, milliseconds, or microseconds before you go debugging the query.

#Converting back: to_timestamp vs FROM_UNIXTIME

Each engine has an inverse function. Postgres uses to_timestamp(epoch) which returns a timestamptz; MySQL uses FROM_UNIXTIME(epoch) which returns a DATETIME in the session zone.

-- Postgres
SELECT to_timestamp(1749254400);

-- MySQL
SELECT FROM_UNIXTIME(1749254400);

The MySQL manual flags that round-tripping through a non-UTC session zone is lossy near DST transitions, because the mapping is not one-to-one. Keep your sessions on UTC for storage and conversion work to sidestep that entirely.

#Side-by-side: type, precision, and timezone

Pulling the differences together makes the migration risks obvious.

#Why bigint seconds is the safest cross-engine choice

The single safest cross-engine convention is to standardize on bigint seconds at the application boundary. Cast Postgres results with ::bigint, take MySQL's integer default as-is, and only reach for fractional precision when you have a concrete need for it. That keeps both databases producing the identical, unambiguous integer for any given instant — and it survives serialization through JSON, CSV exports, and message queues without float drift.

#Exporting timestamp columns without leaking data

If you are moving timestamp columns out of a SQL dump and into a spreadsheet for analysis, the SQL Converter turns CREATE/INSERT statements into .xlsx in the browser, and the CSV ↔ JSON converter handles the export side — both run entirely client-side, so timestamp data from production never leaves your machine.

#Common pitfalls when moving between the two

• Float equality. Comparing extract(epoch ...) values with = can fail on the last microsecond digit. Cast to bigint first.
• Version drift. A Postgres upgrade from 13 to 14+ silently flips extract from float to numeric; driver-level type mapping changes with it.
• Milliseconds vs seconds. The classic 1000× error; a 2026 epoch in seconds is ~1.7 billion, in milliseconds ~1.7 trillion.
• Session zone leaks. UNIX_TIMESTAMP('2026-06-07 12:00:00') depends on the connection zone; the same literal gives different epochs on different sessions.

A quick sanity check: a present-day Unix timestamp in seconds has 10 digits, in milliseconds has 13. If a number has 13 digits where you expected 10, something multiplied by 1000 that should not have.

#References

• PostgreSQL: Date/Time Functions and Operators — epoch field definition for timestamptz vs timestamp, and microsecond precision.
• PostgreSQL 14 Release Notes — the change of extract() from double precision to numeric, with date_part() retaining float.
• MySQL: Date and Time Functions — UNIX_TIMESTAMP() integer vs DECIMAL return rule, session-zone assumption, and lossy round-tripping note.
• MySQL: Fractional Seconds in Time Values — how NOW(6) and fractional precision propagate through functions.

#Related on iKit

• Unix Timestamp Explained: the 10-digit numbers in your logs — how to recognize epoch seconds at a glance, the same skill that catches a seconds-vs-millis mismatch between Postgres and MySQL.
• Why your JavaScript timestamp is 1000× bigger than your backend's — the milliseconds-vs-seconds bug that bites hardest when JS reads database epoch values.
• Convert a Unix timestamp to a date without timezone bugs — turning epoch numbers back into readable dates, the to_timestamp / FROM_UNIXTIME problem from the application side.
• Epoch time cheat sheet: seconds, millis, micros, nanos — the unit table behind why Postgres microseconds and MySQL integers need normalizing.
• The Year 2038 problem: who's affected and how to test — what happens to 32-bit epoch storage, relevant when choosing bigint for timestamp columns.
• How to read JWT exp and iat as Unix timestamps — the same epoch-decoding skill applied to auth tokens instead of database rows.