TO_TIMESTAMP en SQL: parsear cadenas y construir timestamps desde el epoch Unix

El TO_TIMESTAMP de PostgreSQL hace dos trabajos muy distintos bajo un mismo nombre: parsear una cadena segun una plantilla de formato y construir un momento en el tiempo a partir de un numero de segundos del epoch Unix. Ambas formas devuelven timestamptz, y ese unico hecho es la fuente de casi toda la confusion con zonas horarias alrededor de la funcion.

Parsear una cadena por plantilla

El primer modo recibe una cadena y una plantilla de formato. La plantilla indica como leer la entrada: YYYY es el ano, MM el mes, DD el dia, HH24 el reloj de 24 horas, MI los minutos, SS los segundos.

SELECT TO_TIMESTAMP('2024-03-15 14:30', 'YYYY-MM-DD HH24:MI') AS ts;
-- 2024-03-15 14:30:00+00 (with server timezone UTC)

El uso clasico es cargar datos sucios donde la fecha llega como cadena con un formato no estandar:

INSERT INTO users (id, email, name, country, created_at)
VALUES (1, 'kate@example.com', 'Kate', 'DE',
        TO_TIMESTAMP('15/03/2024 09:05', 'DD/MM/YYYY HH24:MI'));

Propiedades practicas del modo plantilla:

• TO_TIMESTAMP es mas indulgente que un cast ::timestamptz: suele tolerar espacios de mas.
• El resultado siempre es timestamptz, anclado al TimeZone actual de la sesion.
• Para una fecha sin hora existe el hermano TO_DATE con el mismo lenguaje de plantillas.

Construir tiempo desde el epoch Unix

El segundo modo recibe un solo numero: los segundos transcurridos desde 1970-01-01 00:00:00 UTC. La parte fraccionaria aporta precision por debajo del segundo.

SELECT TO_TIMESTAMP(1710513000)      AS from_epoch,
       TO_TIMESTAMP(1710513000.5)    AS with_millis;
-- 2024-03-15 14:30:00+00 | 2024-03-15 14:30:00.5+00

Es un salvavidas cuando el tiempo se guarda como bigint (la forma habitual de logs y eventos del backend):

SELECT id, user_id, amount,
       TO_TIMESTAMP(created_at) AS created_ts
FROM orders
WHERE created_at >= 1704067200;  -- since 2024-01-01 UTC

Lo clave: el numero siempre se interpreta como segundos UTC. El instante en si es absoluto; solo su representacion textual depende del TimeZone de la sesion.

Zonas horarias: el gran tropiezo

Ambas formas devuelven timestamptz. Eso significa que internamente se guarda un instante UTC absoluto y al mostrarlo se traduce a la zona de tu sesion. La misma consulta da un texto distinto con otro SET TIME ZONE.

SET TIME ZONE 'UTC';
SELECT TO_TIMESTAMP('2024-03-15 14:30', 'YYYY-MM-DD HH24:MI');
-- 2024-03-15 14:30:00+00

SET TIME ZONE 'Europe/Madrid';
SELECT TO_TIMESTAMP('2024-03-15 14:30', 'YYYY-MM-DD HH24:MI');
-- 2024-03-15 15:30:00+01

Tropiezo: al parsear una cadena la plantilla no lleva zona, asi que los digitos de ano-mes-dia-hora se leen como hora local de la sesion, no como UTC. Si la cadena esta de verdad en UTC, alinealo de forma explicita:

SELECT TO_TIMESTAMP('2024-03-15 14:30', 'YYYY-MM-DD HH24:MI')
         AT TIME ZONE 'UTC' AS naive_utc;

Vuelta con EXTRACT(EPOCH)

El viaje de ida y vuelta se cierra con EXTRACT(EPOCH FROM ...), que devuelve los segundos del epoch Unix de un timestamptz. La pareja TO_TIMESTAMP y EXTRACT(EPOCH ...) es reversible.

SELECT EXTRACT(EPOCH FROM TIMESTAMPTZ '2024-03-15 14:30:00+00') AS epoch;
-- 1710513000

SELECT TO_TIMESTAMP(EXTRACT(EPOCH FROM created_at)) = created_at AS same
FROM orders
LIMIT 1;
-- t

Esto resulta comodo para diferencias y para guardar tiempo en APIs numericas. La edad de un pedido en horas es una resta de epochs:

SELECT id,
       (EXTRACT(EPOCH FROM now()) - EXTRACT(EPOCH FROM created_at)) / 3600.0
         AS age_hours
FROM orders
WHERE status = 'paid';

Diferencias en MySQL y ClickHouse

El nombre TO_TIMESTAMP pertenece al mundo de PostgreSQL y Oracle; otros motores lo escriben distinto.

• MySQL parsea una cadena con STR_TO_DATE(str, format) usando sus propios codigos de formato (%Y, %m, %d, %H, %i). Construye un momento desde el epoch con FROM_UNIXTIME(seconds) y vuelve con UNIX_TIMESTAMP(ts).

SELECT STR_TO_DATE('2024-03-15 14:30', '%Y-%m-%d %H:%i') AS parsed,
       FROM_UNIXTIME(1710513000)                          AS from_epoch;

• ClickHouse usa parseDateTime (sintaxis parecida a MySQL) y fromUnixTimestamp(seconds), con toUnixTimestamp(ts) para el sentido inverso.

SELECT fromUnixTimestamp(1710513000) AS from_epoch,
       toUnixTimestamp(now())        AS to_epoch;

Manten presente la separacion: un solo numero de segundos es un instante UTC absoluto, mientras que una cadena con plantilla siempre arrastra la pregunta de en que zona esta. Manten los dos modos separados en la cabeza y TO_TIMESTAMP dejara de darte sorpresas.