MakeTimestamp logo
MakeTimestamp

Guide

Carimbos de data e hora em bancos de dados: guia de armazenamento, indexação e conversão

Por que esta página

Os carimbos de data e hora orientam quase todas as consultas: ordenação de eventos, filtragem de intervalos e associação de logs. O problema é que cada banco de dados lida com o tempo de maneira diferente. Este guia fornece uma árvore de decisão rápida, esquemas de tabela prontos para envio e conversões para MySQL, PostgreSQL e SQLite, além de CTAs para os conversores completos quando você precisar depurar dados.

Decisões rápidas

  • Sempre armazenar em UTC; formate para local apenas na borda.
  • Precisão: segundos são suficientes para análises; milissegundos (ou mais) para logs/rastreamentos.
  • Tipo: use tipos com reconhecimento de fuso horário quando disponíveis; volte para a época BIGINT quando a interoperabilidade é fundamental.
  • Índices: evite envolver a coluna em funções; pré-calcule colunas derivadas, se necessário.
  • Retenção: particione ou remova dados antigos antes que os índices aumentem.

Tipos recomendados por mecanismo

MySQL

  • Preferencial: TIMESTAMP (intervalo 1970-2038) ou DATETIME (intervalo mais amplo) armazenado como UTC.
  • Para maior precisão ou datas futuras além de 2038: época BIGINT em milissegundos ou microssegundos.
  • Trecho da tabela:
CREATE TABLE events (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  occurred_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  occurred_at_ms BIGINT GENERATED ALWAYS AS (UNIX_TIMESTAMP(occurred_at) * 1000) STORED,
  INDEX idx_events_occurred_at (occurred_at)
);

PostgreSQL

  • Preferencial: TIMESTAMPTZ (armazena UTC, renderiza com fuso horário).
  • Precisão: até microssegundos; suporta colunas GENERATED ALWAYS para épocas.
CREATE TABLE events (
  id BIGSERIAL PRIMARY KEY,
  occurred_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  occurred_at_ms BIGINT GENERATED ALWAYS AS (EXTRACT(EPOCH FROM occurred_at) * 1000)::BIGINT STORED,
  INDEX (occurred_at)
);

SQLite

  • Armazena como TEXT, REAL ou INTEGER; escolha época INTEGER para consistência.
CREATE TABLE events (
  id INTEGER PRIMARY KEY,
  occurred_at_ms INTEGER NOT NULL, -- store UTC milliseconds
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_events_occurred_at_ms ON events (occurred_at_ms);

Convertendo época ↔ tempo humano

MySQL

-- epoch (seconds) -> timestamp
SELECT FROM_UNIXTIME(1704067200) AS utc_time;
-- timestamp -> epoch seconds/milliseconds
SELECT UNIX_TIMESTAMP(occurred_at) AS epoch_s,
       UNIX_TIMESTAMP(occurred_at) * 1000 AS epoch_ms
FROM events;
-- apply timezone safely
SELECT CONVERT_TZ(occurred_at, 'UTC', 'America/New_York') FROM events;

PostgreSQL

-- epoch seconds -> timestamptz (UTC)
SELECT to_timestamp(1704067200) AT TIME ZONE 'UTC';
-- timestamptz -> epoch seconds/milliseconds
SELECT EXTRACT(EPOCH FROM occurred_at) AS epoch_s,
       (EXTRACT(EPOCH FROM occurred_at) * 1000)::BIGINT AS epoch_ms
FROM events;
-- render in a specific zone
SELECT occurred_at AT TIME ZONE 'America/New_York' FROM events;

SQLite

-- epoch milliseconds -> UTC datetime string
SELECT datetime(occurred_at_ms / 1000, 'unixepoch') AS utc_time FROM events;
-- UTC datetime string -> epoch milliseconds
SELECT strftime('%s', '2024-12-01 00:00:00') * 1000 AS epoch_ms;

Precisa de um conversor preciso durante o teste? Vá para Conversor de carimbo de data/hora Unix ou Conversor de carimbo de data/hora em lote.

Padrões de indexação e consulta

  • Use filtros de intervalo (WHERE ocorreu_at >= ... AND ocorreu_at < ...) para permanecer sargável.
  • Evite agrupar a coluna em DATE(), CAST() ou ::date em filtros — use colunas computadas/geradas se precisar de agrupamentos em nível de dia.
  • Para partições quentes, considere índices compostos (occurred_at, user_id) ao filtrar por ambos.
  • Particionamento/retenção: mantenha as partições recentes pequenas; arquivar partições antigas em armazenamento mais barato.
  • Ordenação: ORDER BY ocorreu_at DESC LIMIT 100 é amigável ao índice quando a coluna inicial é occurred_at.

Armadilhas comuns (e soluções)

  • limite de 2038 (MySQL TIMESTAMP): use DATETIME(3) ou época BIGINT para linhas com datas futuras.
  • Desvio de fuso horário da sessão: defina time_zone='+00:00' (MySQL) ou SET TIMEZONE='UTC'; (PostgreSQL) na inicialização da conexão.
  • Precisão mista: nunca armazene segundos e milissegundos na mesma coluna; impor comprimento na ingestão.
  • Filtros agrupados por função: WHERE DATE(occurred_at)=... desativa índices; pré-calcular occurred_at_date em vez disso.
  • Surpresas de horário de verão nos relatórios: armazenar UTC; localizar na camada de visualização com zonas reais da IANA.

Lista de verificação de migração

  1. Congelar gravações ou rotear através de uma camada de gravação dupla.
  2. Adicione uma nova coluna UTC (por exemplo, occurred_at_utc TIMESTAMPTZ ou occurred_at_ms BIGINT).
  3. Backfill com conversão determinística; verificar com verificações pontuais.
  4. Adicione índices na nova coluna e alterne as leituras para ela.
  5. Remover coluna herdada após monitoramento e verificação de preenchimento.

Perguntas frequentes

  • Devo armazenar como época ou data e hora? Epoch BIGINT é ótimo para interoperabilidade e precisão; TIMESTAMPTZ é mais seguro para matemática e legibilidade de datas integradas.
  • Que precisão devo escolher? Logs/rastreamentos: ms ou superior. Dados/análises de negócios: segundos geralmente são suficientes.
  • Como faço para impedir que os usuários enviem a hora local? Normalize no limite: aceite a entrada com fuso horário, converta para UTC e armazene apenas UTC.
  • Como validar carimbos de data/hora de entrada? Aplicar cargas úteis somente de dígitos, detecção de precisão baseada em comprimento e intervalo razoável (por exemplo, 2000-01-01 a 2100-01-01). Consulte Níveis de precisão do carimbo de data/hora.

Ferramentas e guias relacionados