EXTRACT(EPOCH FROM ...) en SQL: duraciones en segundos y tiempo Unix

EXTRACT(EPOCH FROM ...) responde a una de las preguntas mas habituales de la analitica: "cuantos segundos han pasado?". Sobre un interval devuelve su duracion completa en segundos; sobre una marca de tiempo devuelve el tiempo Unix, los segundos desde 1970-01-01 00:00:00 UTC. Recurres a el para medir cuanto duro un proceso, para expresar un SLA en segundos o minutos, o para convertir una fecha en un numero plano para almacenamiento, ordenacion e intercambio con colas, logs y APIs externas.

El atractivo de EPOCH es que el tiempo se convierte en un numero double precision corriente que puedes dividir, promediar, sumar y comparar como cualquier otra metrica. Este articulo recorre cuatro casos practicos y termina con un tropiezo de timestamptz y zonas horarias.

Dos modos: intervalo frente a marca de tiempo

El comportamiento de EPOCH depende del tipo del argumento, y ese es el punto clave: la misma expresion devuelve o bien una duracion, o bien un momento absoluto.

• Sobre un interval, el resultado es la duracion total de ese intervalo en segundos. interval '1 day' da 86400, los segundos que tiene un dia.
• Sobre un timestamp, el resultado es el tiempo Unix, los segundos desde la epoca de 1970. Es un numero enorme, no una duracion.

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

El tipo del resultado es double precision, asi que los segundos fraccionarios (milisegundos) se conservan. No confundas los dos modos: 86400 es la longitud de un dia, mientras que 1781697600 es el momento concreto 2026-06-17 12:00 UTC en la linea de tiempo Unix. Mezclar la duracion de un intervalo con el tiempo Unix de una marca en una misma expresion produce un numero sin sentido, asi que antes de cada EPOCH preguntate: estoy midiendo una duracion o fijando un punto en el tiempo?

Duracion entre dos marcas de tiempo

El patron mas comun es restar una marca de tiempo de otra para medir el tiempo transcurrido. Restar dos timestamp produce un interval, y EPOCH lo convierte en segundos.

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

Para obtener minutos u horas, divide el resultado entre 60 o 3600:

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

Divide entre 3600.0 con el punto decimal, no entre 3600. Aunque EPOCH ya devuelve double precision y la division es fraccionaria, la costumbre del .0 te salva en expresiones donde el numerador resulta ser un entero y la division entera descartaria el resto.

Agregados: tiempo medio de finalizacion

EPOCH encaja limpiamente en las funciones de agregacion. Calculemos el tiempo medio de envio en horas por pais, uniendo orders con 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 convierte cada diferencia a segundos, AVG promedia esos numeros y solo entonces divides entre 3600. Es mas fiable que intentar promediar los propios intervalos. El mismo truco vale para medianas y percentiles con percentile_cont(0.5): calcula las duraciones en segundos y deja la conversion a horas o minutos para el final.

De vuelta: de segundos a una marca de tiempo

Si una columna guarda el tiempo Unix como numero, to_timestamp lo convierte en una fecha legible. Es la inversa de EXTRACT(EPOCH FROM timestamp). Dale el mismo 1781697600 que produjimos arriba y deberias volver al momento original:

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

El recorrido se cierra: EXTRACT(EPOCH FROM ...) convirtio 2026-06-17 12:00:00 en 1781697600, y to_timestamp devuelve ese mismo momento en UTC. Tambien sirve para aritmetica: suma un numero fijo de segundos y vuelve a una marca de tiempo.

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

to_timestamp toma el tiempo Unix y devuelve un timestamptz en UTC, asi que como se muestra despues el momento depende de la zona horaria de la sesion.

Tropiezo: EPOCH sobre timestamptz y zonas horarias

La gran confusion es sobre los tipos. EXTRACT(EPOCH FROM ...) sobre un timestamptz siempre cuenta desde la epoca en UTC, lo cual no es ambiguo. Sobre un timestamp "ingenuo" (sin zona), PostgreSQL tambien trata el valor como UTC, lo que puede no coincidir con tu expectativa si los datos se guardaron en una zona local: la misma fila da un tiempo Unix distinto segun el tipo que elijas.

• Para timestamptz, el resultado es estable e independiente del TimeZone de la sesion.
• MySQL no tiene un EXTRACT(EPOCH ...) directo: usa UNIX_TIMESTAMP(ts) para el tiempo Unix y TIMESTAMPDIFF(SECOND, a, b) para la duracion.
• ClickHouse usa toUnixTimestamp(ts) para el tiempo Unix y dateDiff('second', a, b) para una diferencia.

-- MySQL: duracion en segundos
SELECT TIMESTAMPDIFF(SECOND, created_at, shipped_at) AS seconds_to_ship
FROM orders
WHERE status = 'shipped';

Los problemas con EPOCH casi siempre viven en los datos limite, no en la funcion: timestamp sin zona frente a timestamptz con zona, intervalos negativos cuando shipped_at precede a created_at, y perdida de precision al redondear luego los segundos a horas. Antes de portar una consulta entre PostgreSQL, MySQL y ClickHouse, ejecutala en una tabla pequena con NULL, duracion cero y una fecha limite: en el "camino feliz" los motores coinciden y divergen justo en esos valores. Una conclusion practica: si un numero de EPOCH afecta a dinero, a un SLA o a un identificador externo, no dejes su significado implicito. Y vigila el rendimiento: EXTRACT(EPOCH FROM ts) en un WHERE envuelve la columna en una funcion y a menudo bloquea un indice normal sobre ts, asi que para filtros por rango de fechas compara la columna con los limites y reserva EPOCH para los calculos del SELECT.

Ten presente la division: un intervalo da una duracion, una marca de tiempo da un momento. El mismo EPOCH, pero el significado del numero es el opuesto exacto.