SQL EXTRACT(EPOCH FROM ...): Durations in Seconds and Unix Time

EXTRACT(EPOCH FROM ...) answers one of analytics' most common questions: "how many seconds?" Over an interval it returns the full duration in seconds; over a timestamp it returns Unix time, the number of seconds since 1970-01-01 00:00:00 UTC. You reach for it to measure how long a process ran, to express an SLA in seconds or minutes, or to turn a date into a plain number for storage, sorting, and exchange with queues, logs, and external APIs.

The appeal of EPOCH is that time becomes an ordinary double precision number you can divide, average, add, and compare like any other metric. This article walks through four practical cases and ends with a timestamptz and time-zone gotcha.

Two modes: interval versus timestamp

The behavior of EPOCH depends on the argument's type, and that is the crux: the same expression returns either a duration or an absolute moment.

• Over an interval, the result is the total duration of that interval in seconds. interval '1 day' yields 86400, the number of seconds in a day.
• Over a timestamp, the result is Unix time, the seconds since the 1970 epoch. It is a huge number, not a duration.

SELECT
  EXTRACT(EPOCH FROM INTERVAL '1 day')                 AS interval_seconds, -- 86400
  EXTRACT(EPOCH FROM TIMESTAMP '2026-06-17 12:00:00')  AS unix_seconds;     -- 1781697600

The result type is double precision, so fractional seconds (milliseconds) survive. Do not conflate the two modes: 86400 is the length of a day, while 1781697600 is the specific moment 2026-06-17 12:00 UTC on the Unix timeline. Mixing an interval's duration with a timestamp's Unix time in one expression yields a meaningless number like "86400 plus a billion and a half," so before each EPOCH ask yourself: am I measuring a duration or pinning a point in time?

Duration between two timestamps

The most common pattern is subtracting one timestamp from another to measure elapsed time. Subtracting two timestamp values yields an interval, and EPOCH turns it into seconds.

SELECT
  id,
  EXTRACT(EPOCH FROM (shipped_at - created_at)) AS seconds_to_ship
FROM orders
WHERE status = 'shipped';

To get minutes or hours, divide the result by 60 or 3600:

SELECT
  id,
  EXTRACT(EPOCH FROM (shipped_at - created_at)) / 3600.0 AS hours_to_ship
FROM orders
WHERE status = 'shipped';

Divide by 3600.0 with the decimal point, not by 3600. While EPOCH itself returns double precision so the division is already fractional, the .0 habit saves you in expressions where the numerator turns out to be an integer and integer division would drop the remainder.

Aggregates: average completion time

EPOCH slots cleanly into aggregate functions. Let's compute the average shipping time in hours per country by joining orders to users:

SELECT
  u.country,
  AVG(EXTRACT(EPOCH FROM (o.shipped_at - o.created_at))) / 3600.0 AS avg_hours
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'shipped'
GROUP BY u.country
ORDER BY avg_hours DESC;

EPOCH converts each difference to seconds, AVG averages those numbers, and only then do you divide by 3600. That is more reliable than averaging the intervals themselves. The same trick works for medians and percentiles via percentile_cont(0.5): compute durations in seconds and leave the conversion to hours or minutes for the very end.

Going back: seconds to a timestamp

If a column stores Unix time as a number, to_timestamp turns it into a human-readable date. It is the inverse of EXTRACT(EPOCH FROM timestamp). Feed it the same 1781697600 we produced above and you should land back on the original moment:

SELECT
  to_timestamp(1781697600) AS as_timestamp; -- 2026-06-17 12:00:00+00

The round-trip closes: EXTRACT(EPOCH FROM ...) turned 2026-06-17 12:00:00 into 1781697600, and to_timestamp returns that same moment in UTC. It is handy for arithmetic too: add a fixed number of seconds and round-trip back to a timestamp.

SELECT
  to_timestamp(
    EXTRACT(EPOCH FROM created_at) + 3600
  ) AS one_hour_later
FROM users
LIMIT 5;

to_timestamp takes Unix time and returns a timestamptz in UTC, so how the moment displays afterward depends on the session time zone.

Gotcha: EPOCH over timestamptz and time zones

The big confusion is about types. EXTRACT(EPOCH FROM ...) on a timestamptz always counts from the epoch in UTC, which is unambiguous. On a "naive" timestamp (no zone), PostgreSQL also treats the value as UTC, which may not match your expectation if the data was actually stored in a local zone: the same row yields a different Unix time depending on which type you chose.

• For timestamptz, the result is stable and independent of the session TimeZone.
• MySQL has no direct EXTRACT(EPOCH ...): use UNIX_TIMESTAMP(ts) for Unix time and TIMESTAMPDIFF(SECOND, a, b) for duration.
• ClickHouse uses toUnixTimestamp(ts) for Unix time and dateDiff('second', a, b) for a difference.

-- MySQL: duration in seconds
SELECT TIMESTAMPDIFF(SECOND, created_at, shipped_at) AS seconds_to_ship
FROM orders
WHERE status = 'shipped';

EPOCH problems almost always live in the boundary data, not the function itself: zoneless timestamp versus zoned timestamptz, negative intervals when shipped_at precedes created_at, and precision loss when you later round seconds to hours. Before porting a query across PostgreSQL, MySQL, and ClickHouse, run it on a tiny table with NULL, zero duration, and an edge-case date: engines agree on the happy path and diverge exactly on those values. One practical takeaway: if a number from EPOCH drives money, an SLA, or an external identifier, do not leave its meaning implicit. And mind performance: EXTRACT(EPOCH FROM ts) in a WHERE clause wraps the column in a function and often blocks a plain index on ts, so for date-range filters compare the column to bounds and keep EPOCH for computations in SELECT.

Keep the split in mind: an interval gives a duration, a timestamp gives a moment. Same EPOCH, but the meaning of the number is the polar opposite.