Um tipo guarda um momento real da historia; o outro guarda simples digitos numa parede, sem zona associada. Escolher entre timestamptz e timestamp decide se seus dados batem para usuarios de fusos diferentes ou se desviam em silencio. Vamos ao concreto.
Um instante versus um relogio de parede
timestamptz (apelido timestamp with time zone) guarda um instante absoluto: por dentro e UTC, e a zona da sessao e aplicada so na exibicao. timestamp (sem zona) e um "relogio de parede ingenuo": o numero 2024-03-15 10:00 sem responder "10 da manha, onde exatamente".
SET timezone = 'UTC';
SELECT
now()::timestamptz AS instant,
'2024-03-15 10:00'::timestamp AS wall_time;
O mesmo timestamptz aparece diferente conforme a sessao, mas e um unico 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 eventos quase sempre sao timestamptz
Qualquer coluna do tipo "quando isso aconteceu", um cadastro, um pagamento, uma linha de log, e um momento da historia. Se voce a guarda como timestamp, perde a zona, e duas linhas de Lisboa e de Moscou ficam incomparaveis.
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()
);
Assim as consultas por intervalo ficam corretas seja qual for a zona do servidor:
SELECT count(*)
FROM orders
WHERE created_at >= '2024-03-01 00:00+00'
AND created_at < '2024-04-01 00:00+00';
Um timestamp sem zona se justifica raramente: uma "data de nascimento", um "a loja abre as 09:00", coisas identicas em qualquer zona que nao nomeiam um instante concreto.
O bug silencioso ao misturar os tipos
A armadilha mais perigosa e atribuir uma string "crua" sem zona a uma coluna timestamptz. O PostgreSQL nao da erro: ele preenche a zona em silencio a partir da sessao atual. Mude a sessao e o significado dos dados muda junto.
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');
As duas linhas parecem identicas, mas sao instantes diferentes, separados por horas.
Pegadinha: um cast timestamptz::timestamp (e o inverso) nao converte o relogio, ele so remove ou acrescenta uma zona pelas regras da sessao. Comparar um timestamptz com um timestamp tambem coage um deles em silencio pela zona da sessao. Defina sempre a zona explicita no literal ('... +00') ou fixe SET timezone = 'UTC'.
Guardar em UTC, exibir na zona do cliente
A regra de trabalho: guarde tudo como timestamptz e faca a conversao de apresentacao com o operador AT TIME ZONE na saida.
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 e um comutador de tipo:
timestamptz AT TIME ZONE 'zone' da um timestamp (o relogio de parede naquela zona);timestamp AT TIME ZONE 'zone' da um timestamptz (trata a hora ingenua como local daquela zona).
Agrupar pelo "dia local" de um funcionario fica assim:
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;
A tabela employees(id, name, manager_id, dept, salary) liga aqui cada pedido a um time.
Diferencas no MySQL e no ClickHouse
- MySQL: o tipo
TIMESTAMP guarda UTC e converte conforme @@session.time_zone, o que se aproxima mais do timestamptz, mas seu intervalo e limitado a 1970-2038. O tipo DATETIME nao guarda zona nenhuma (o analogo do timestamp ingenuo). Para eventos costuma-se escolher TIMESTAMP, ou DATETIME mais uma coluna de zona separada.
- ClickHouse:
DateTime guarda tempo Unix (UTC) e pode carregar um nome de zona como parametro do tipo, DateTime('Europe/Berlin'); a zona afeta o parsing e a exibicao, nao o valor guardado. Para precisao de fracoes de segundo use DateTime64.
Resumindo: eventos vao em timestamptz, o timestamp ingenuo e so para dados independentes de zona, a conversao vai com AT TIME ZONE na saida, e declare sempre a zona nos literais para nunca pegar um deslocamento silencioso.
Um tipo guarda um momento real da historia; o outro guarda simples digitos numa parede, sem zona associada. Escolher entre
timestamptzetimestampdecide se seus dados batem para usuarios de fusos diferentes ou se desviam em silencio. Vamos ao concreto.Um instante versus um relogio de parede
timestamptz(apelidotimestamp with time zone) guarda um instante absoluto: por dentro e UTC, e a zona da sessao e aplicada so na exibicao.timestamp(sem zona) e um "relogio de parede ingenuo": o numero2024-03-15 10:00sem responder "10 da manha, onde exatamente".SET timezone = 'UTC'; SELECT now()::timestamptz AS instant, -- absolute, stored as UTC '2024-03-15 10:00'::timestamp AS wall_time; -- naive, no zoneO mesmo
timestamptzaparece diferente conforme a sessao, mas e um unico 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 eventos quase sempre sao timestamptz
Qualquer coluna do tipo "quando isso aconteceu", um cadastro, um pagamento, uma linha de log, e um momento da historia. Se voce a guarda como
timestamp, perde a zona, e duas linhas de Lisboa e de Moscou ficam incomparaveis.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() );Assim as consultas por intervalo ficam corretas seja qual for a zona do servidor:
SELECT count(*) FROM orders WHERE created_at >= '2024-03-01 00:00+00' AND created_at < '2024-04-01 00:00+00';Um
timestampsem zona se justifica raramente: uma "data de nascimento", um "a loja abre as 09:00", coisas identicas em qualquer zona que nao nomeiam um instante concreto.O bug silencioso ao misturar os tipos
A armadilha mais perigosa e atribuir uma string "crua" sem zona a uma coluna
timestamptz. O PostgreSQL nao da erro: ele preenche a zona em silencio a partir da sessao atual. Mude a sessao e o significado dos dados muda junto.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 UTCAs duas linhas parecem identicas, mas sao instantes diferentes, separados por horas.
Guardar em UTC, exibir na zona do cliente
A regra de trabalho: guarde tudo como
timestamptze faca a conversao de apresentacao com o operadorAT TIME ZONEna saida.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 ZONEe um comutador de tipo:timestamptz AT TIME ZONE 'zone'da umtimestamp(o relogio de parede naquela zona);timestamp AT TIME ZONE 'zone'da umtimestamptz(trata a hora ingenua como local daquela zona).Agrupar pelo "dia local" de um funcionario fica assim:
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;A tabela
employees(id, name, manager_id, dept, salary)liga aqui cada pedido a um time.Diferencas no MySQL e no ClickHouse
TIMESTAMPguarda UTC e converte conforme@@session.time_zone, o que se aproxima mais dotimestamptz, mas seu intervalo e limitado a 1970-2038. O tipoDATETIMEnao guarda zona nenhuma (o analogo dotimestampingenuo). Para eventos costuma-se escolherTIMESTAMP, ouDATETIMEmais uma coluna de zona separada.DateTimeguarda tempo Unix (UTC) e pode carregar um nome de zona como parametro do tipo,DateTime('Europe/Berlin'); a zona afeta o parsing e a exibicao, nao o valor guardado. Para precisao de fracoes de segundo useDateTime64.Resumindo: eventos vao em
timestamptz, otimestampingenuo e so para dados independentes de zona, a conversao vai comAT TIME ZONEna saida, e declare sempre a zona nos literais para nunca pegar um deslocamento silencioso.