Convertir timestamps Unix en PostgreSQL: to_timestamp, epoch, zonas
PostgreSQL tiene el mejor manejo de fechas y horas de cualquier base de datos seria, con una pega persistente: la aritmética de epoch ignora zonas horarias, pero la visualización no. Saber en qué lado estás mata todos los bugs de la hora desfasada.
to_timestamp pasa de epoch en segundos a timestamptz
to_timestamp(double precision) recibe segundos Unix y devuelve un timestamptz (timestamp con zona horaria). La doc de Postgres lo redacta como "segundos desde 1970-01-01 00:00:00 UTC", y eso es exactamente lo que guarda.
SELECT to_timestamp(1763500800);
/* 2025-11-19 00:00:00+00 */Si tu epoch va en milisegundos (cualquier código JavaScript, vamos) divide por 1000 antes. Si va en microsegundos (algunos exports de Cassandra, BigQuery) divide por 1.000.000. Mezclarlos sin avisar te deja en el año 56.000 o en el año 19.701 y el síntoma es "el gráfico sale vacío".
EXTRACT(EPOCH FROM ...) hace el camino inverso
Para sacar un timestamp Unix de un timestamptz usa EXTRACT(EPOCH FROM ...). Devuelve un double precision con segundos, decimales incluidos.
```sql SELECT EXTRACT(EPOCH FROM NOW()); /* 1763500837.421 */
SELECT (EXTRACT(EPOCH FROM NOW()) * 1000)::bigint; /* 1763500837421 (milis, como Date.now en JS) */ ```
Si guardas timestamp (sin zona) en lugar de timestamptz, EXTRACT EPOCH lo trata como si fuera UTC, sin importar el TimeZone de la sesión. Es una fuente típica de líos: el tipo de columna manda sobre la sesión.
Usa timestamptz siempre, timestamp casi nunca
timestamp without time zone es lo que la gente elige por defecto y lo que se arrepiente seis meses después. Guarda una hora de pared sin zona pegada, así que el mismo valor son instantes distintos en sesiones distintas.
timestamptz (alias de timestamp with time zone) guarda un instante en UTC y lo renderiza en el TimeZone de la sesión al mostrarlo. Dos servicios en regiones distintas leen la misma fila y los dos ven hora local correcta. Eso es lo que quieres.
Migrar de timestamp a timestamptz es un cambio de esquema real y tienes que indicar cómo se interpretan los valores ya guardados. Elige AT TIME ZONE 'UTC' si tus datos antiguos ya iban en UTC; elige la zona de origen real en caso contrario.
Cuidado con los milisegundos en extensiones
Varias extensiones populares y algunos ORMs guardan epoch en milisegundos dentro de un bigint en lugar de usar timestamptz. Funciona, pero te saltas todas las herramientas bonitas de Postgres.
/* Bigint con epoch en milis a un timestamptz real */
SELECT to_timestamp(created_at_ms / 1000.0) AS created_at
FROM events;Si controlas el esquema, guarda como timestamptz y olvídate de los epoch enteros. Si te toca convivir con la columna en milis, monta una vista que exponga una columna derivada timestamptz y escribe el resto de la app contra esa vista.
Ejemplo completo
sql/* Epoch en segundos a timestamptz */
SELECT to_timestamp(1763500800);
/* 2025-11-19 00:00:00+00 */
/* Epoch en milisegundos (estilo JavaScript) */
SELECT to_timestamp(1763500837421 / 1000.0);
/* Ahora a epoch en milisegundos */
SELECT (EXTRACT(EPOCH FROM NOW()) * 1000)::bigint;
/* Filtrar filas de la última hora usando epoch como entrada */
SELECT id
FROM events
WHERE created_at >= to_timestamp(1763497237);¿Solo necesitas el resultado?
Cuando solo quieres traducir un epoch Unix que has sacado de una línea de log a una fecha legible, abrir psql es exagerado. Pega el número en el convertidor de timestamps en navegador y mira segundos, milisegundos, ISO 8601, UTC y tu hora local a la vez, sin viajes al servidor.
Abrir Convertidor de Timestamp Unix →Preguntas frecuentes
¿TIMESTAMP o TIMESTAMPTZ?
TIMESTAMPTZ. Guarda el instante en UTC y se renderiza en la zona de la sesión. TIMESTAMP a secas guarda una hora de pared sin zona; la misma fila significa instantes distintos para clientes distintos, lo cual casi nunca es lo que quieres.
¿Por qué to_timestamp me da un valor con una hora de desfase?
O pasaste milisegundos sin dividir por 1000, o tu TimeZone de sesión no es UTC y estás leyendo una columna timestamp tratada como local. Fija TimeZone explícitamente o usa AT TIME ZONE 'UTC' en la proyección.
¿Cómo paso strings ISO 8601 a timestamptz?
Cast directo: '2025-11-19T00:00:00Z'::timestamptz. PostgreSQL parsea RFC 3339 / ISO 8601 nativamente, incluido el Z final y offsets tipo +02:00. Si el string es inválido, salta error en el cast.