Un tipo guarda un momento real de la historia; el otro guarda simples cifras en una pared, sin zona asociada. Elegir entre timestamptz y timestamp decide si tus datos cuadran para usuarios de distintas zonas horarias o si se desvian en silencio. Vamos a lo concreto.
Un instante frente a un reloj de pared
timestamptz (alias timestamp with time zone) guarda un instante absoluto: por dentro es UTC, y la zona de la sesion se aplica solo al mostrarlo. timestamp (sin zona) es un "reloj de pared ingenuo": el numero 2024-03-15 10:00 sin responder a "las 10 de la manana, donde exactamente".
SET timezone = 'UTC';
SELECT
now()::timestamptz AS instant,
'2024-03-15 10:00'::timestamp AS wall_time;
El mismo timestamptz se ve distinto segun la sesion, pero es un solo instante:
SET timezone = 'America/New_York';
SELECT '2024-03-15 10:00+00'::timestamptz;
SET timezone = 'Europe/Moscow';
SELECT '2024-03-15 10:00+00'::timestamptz;
Por que los eventos casi siempre son timestamptz
Cualquier columna de tipo "cuando paso esto", un alta, un pago, una linea de log, es un momento de la historia. Si la guardas como timestamp pierdes la zona, y dos filas de Lisboa y de Moscu se vuelven incomparables.
CREATE TABLE users (
id bigserial PRIMARY KEY,
email text NOT NULL UNIQUE,
name text,
country text,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE orders (
id bigserial PRIMARY KEY,
user_id bigint NOT NULL REFERENCES users(id),
amount numeric(12,2) NOT NULL,
status text NOT NULL DEFAULT 'new',
created_at timestamptz NOT NULL DEFAULT now()
);
Asi las consultas por rango son correctas sin importar la zona del servidor:
SELECT count(*)
FROM orders
WHERE created_at >= '2024-03-01 00:00+00'
AND created_at < '2024-04-01 00:00+00';
Un timestamp sin zona se justifica pocas veces: una "fecha de nacimiento", un "la tienda abre a las 09:00", cosas identicas en cualquier zona que no nombran un instante concreto.
El error silencioso al mezclar los tipos
La trampa mas peligrosa es asignar una cadena "desnuda" sin zona a una columna timestamptz. PostgreSQL no da error: rellena la zona en silencio desde la sesion actual. Cambia la sesion y cambia el significado de los datos.
SET timezone = 'America/New_York';
INSERT INTO orders (user_id, amount, created_at)
VALUES (1, 50.00, '2024-03-15 10:00');
SET timezone = 'UTC';
INSERT INTO orders (user_id, amount, created_at)
VALUES (1, 50.00, '2024-03-15 10:00');
Las dos filas parecen identicas, pero son instantes distintos, separados por horas.
Trampa: un cast timestamptz::timestamp (y el inverso) no convierte el reloj, solo quita o anade una zona con las reglas de la sesion. Comparar un timestamptz con un timestamp tambien fuerza en silencio uno a traves de la zona de la sesion. Pon siempre la zona explicita en el literal ('... +00') o fija SET timezone = 'UTC'.
Guardar en UTC, mostrar en la zona del cliente
La regla de trabajo: guarda todo como timestamptz y haz la conversion de presentacion con el operador AT TIME ZONE en la salida.
SELECT
id,
created_at,
created_at AT TIME ZONE 'Europe/Berlin' AS berlin_wall_time
FROM orders
ORDER BY created_at DESC
LIMIT 5;
AT TIME ZONE es un conmutador de tipo:
timestamptz AT TIME ZONE 'zone' da un timestamp (el reloj de pared en esa zona);timestamp AT TIME ZONE 'zone' da un timestamptz (interpreta la hora ingenua como local de esa zona).
Agrupar por el "dia local" de un empleado se ve asi:
SELECT
e.dept,
date_trunc('day', o.created_at AT TIME ZONE 'Europe/Berlin') AS local_day,
sum(o.amount) AS revenue
FROM orders o
JOIN employees e ON e.id = o.user_id
GROUP BY e.dept, local_day
ORDER BY local_day;
La tabla employees(id, name, manager_id, dept, salary) enlaza aqui cada pedido con un equipo.
Diferencias en MySQL y ClickHouse
- MySQL: el tipo
TIMESTAMP guarda UTC y convierte segun @@session.time_zone, lo que se parece mas a timestamptz, pero su rango se limita a 1970-2038. El tipo DATETIME no guarda zona alguna (el analogo del timestamp ingenuo). Para eventos se suele elegir TIMESTAMP, o DATETIME mas una columna de zona aparte.
- ClickHouse:
DateTime guarda tiempo Unix (UTC) y puede llevar un nombre de zona como parametro del tipo, DateTime('Europe/Berlin'); la zona afecta al parseo y a la presentacion, no al valor guardado. Para precision de fracciones de segundo usa DateTime64.
En resumen: los eventos van en timestamptz, el timestamp ingenuo es solo para datos independientes de zona, la conversion va con AT TIME ZONE en la salida, y pon siempre la zona en los literales para no pillar un desplazamiento silencioso.
Un tipo guarda un momento real de la historia; el otro guarda simples cifras en una pared, sin zona asociada. Elegir entre
timestamptzytimestampdecide si tus datos cuadran para usuarios de distintas zonas horarias o si se desvian en silencio. Vamos a lo concreto.Un instante frente a un reloj de pared
timestamptz(aliastimestamp with time zone) guarda un instante absoluto: por dentro es UTC, y la zona de la sesion se aplica solo al mostrarlo.timestamp(sin zona) es un "reloj de pared ingenuo": el numero2024-03-15 10:00sin responder a "las 10 de la manana, donde exactamente".SET timezone = 'UTC'; SELECT now()::timestamptz AS instant, -- absolute, stored as UTC '2024-03-15 10:00'::timestamp AS wall_time; -- naive, no zoneEl mismo
timestamptzse ve distinto segun la sesion, pero es un solo instante:SET timezone = 'America/New_York'; SELECT '2024-03-15 10:00+00'::timestamptz; -- shows 2024-03-15 06:00:00-04 SET timezone = 'Europe/Moscow'; SELECT '2024-03-15 10:00+00'::timestamptz; -- shows 2024-03-15 13:00:00+03Por que los eventos casi siempre son timestamptz
Cualquier columna de tipo "cuando paso esto", un alta, un pago, una linea de log, es un momento de la historia. Si la guardas como
timestamppierdes la zona, y dos filas de Lisboa y de Moscu se vuelven incomparables.CREATE TABLE users ( id bigserial PRIMARY KEY, email text NOT NULL UNIQUE, name text, country text, created_at timestamptz NOT NULL DEFAULT now() ); CREATE TABLE orders ( id bigserial PRIMARY KEY, user_id bigint NOT NULL REFERENCES users(id), amount numeric(12,2) NOT NULL, status text NOT NULL DEFAULT 'new', created_at timestamptz NOT NULL DEFAULT now() );Asi las consultas por rango son correctas sin importar la zona del servidor:
SELECT count(*) FROM orders WHERE created_at >= '2024-03-01 00:00+00' AND created_at < '2024-04-01 00:00+00';Un
timestampsin zona se justifica pocas veces: una "fecha de nacimiento", un "la tienda abre a las 09:00", cosas identicas en cualquier zona que no nombran un instante concreto.El error silencioso al mezclar los tipos
La trampa mas peligrosa es asignar una cadena "desnuda" sin zona a una columna
timestamptz. PostgreSQL no da error: rellena la zona en silencio desde la sesion actual. Cambia la sesion y cambia el significado de los datos.SET timezone = 'America/New_York'; INSERT INTO orders (user_id, amount, created_at) VALUES (1, 50.00, '2024-03-15 10:00'); -- interpreted as 10:00 New York SET timezone = 'UTC'; INSERT INTO orders (user_id, amount, created_at) VALUES (1, 50.00, '2024-03-15 10:00'); -- interpreted as 10:00 UTCLas dos filas parecen identicas, pero son instantes distintos, separados por horas.
Guardar en UTC, mostrar en la zona del cliente
La regla de trabajo: guarda todo como
timestamptzy haz la conversion de presentacion con el operadorAT TIME ZONEen la salida.SELECT id, created_at, -- instant (UTC under the hood) created_at AT TIME ZONE 'Europe/Berlin' AS berlin_wall_time FROM orders ORDER BY created_at DESC LIMIT 5;AT TIME ZONEes un conmutador de tipo:timestamptz AT TIME ZONE 'zone'da untimestamp(el reloj de pared en esa zona);timestamp AT TIME ZONE 'zone'da untimestamptz(interpreta la hora ingenua como local de esa zona).Agrupar por el "dia local" de un empleado se ve asi:
SELECT e.dept, date_trunc('day', o.created_at AT TIME ZONE 'Europe/Berlin') AS local_day, sum(o.amount) AS revenue FROM orders o JOIN employees e ON e.id = o.user_id GROUP BY e.dept, local_day ORDER BY local_day;La tabla
employees(id, name, manager_id, dept, salary)enlaza aqui cada pedido con un equipo.Diferencias en MySQL y ClickHouse
TIMESTAMPguarda UTC y convierte segun@@session.time_zone, lo que se parece mas atimestamptz, pero su rango se limita a 1970-2038. El tipoDATETIMEno guarda zona alguna (el analogo deltimestampingenuo). Para eventos se suele elegirTIMESTAMP, oDATETIMEmas una columna de zona aparte.DateTimeguarda tiempo Unix (UTC) y puede llevar un nombre de zona como parametro del tipo,DateTime('Europe/Berlin'); la zona afecta al parseo y a la presentacion, no al valor guardado. Para precision de fracciones de segundo usaDateTime64.En resumen: los eventos van en
timestamptz, eltimestampingenuo es solo para datos independientes de zona, la conversion va conAT TIME ZONEen la salida, y pon siempre la zona en los literales para no pillar un desplazamiento silencioso.