MakeTimestamp logo
MakeTimestamp

Guías

Timestamps en bases de datos: guía de almacenamiento, indexación y conversión

Por qué esta página

Los timestamps impulsan casi todas las consultas: ordenar eventos, filtrar rangos y unir logs. El problema es que cada base de datos maneja el tiempo de forma distinta. Esta guía te da un árbol de decisión rápido, esquemas listos para producción y conversiones para MySQL, PostgreSQL y SQLite—además de enlaces a convertidores cuando necesites depurar datos.

Decisiones rápidas

  • Almacena siempre en UTC; convierte a local solo en la capa de presentación.
  • Precisión: segundos para analítica; milisegundos (o más) para logs/trazas.
  • Tipo: usa tipos con zona horaria cuando existan; si la interoperabilidad es clave, usa epoch en BIGINT.
  • Índices: evita envolver la columna con funciones; precalcula columnas derivadas si es necesario.
  • Retención: particiona o depura datos antiguos antes de que los índices se inflen.

Tipos recomendados por motor

MySQL

  • Preferido: TIMESTAMP (rango 1970-2038) o DATETIME (rango más amplio) almacenado en UTC.
  • Para mayor precisión o fechas futuras: epoch BIGINT en milisegundos o microsegundos.
  • Ejemplo de tabla:
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

  • Preferido: TIMESTAMPTZ (almacena UTC, renderiza con zona).
  • Precisión: hasta microsegundos; soporta columnas GENERATED ALWAYS para epoch.
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

  • Almacena como TEXT, REAL o INTEGER; elige INTEGER epoch para consistencia.
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);

Conversión epoch ↔ tiempo legible

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;

¿Necesitas un convertidor preciso mientras pruebas? Ve a Unix Timestamp Converter o Batch Timestamp Converter.

Indexación y patrones de consulta

  • Usa filtros por rango (WHERE occurred_at >= ... AND occurred_at < ...) para mantener consultas sargables.
  • Evita envolver la columna con DATE(), CAST() o ::date; usa columnas calculadas si necesitas agregaciones por día.
  • Para particiones calientes, considera índices compuestos (occurred_at, user_id) cuando filtras por ambos.
  • Particionado/retención: mantén particiones recientes pequeñas; archiva las antiguas en almacenamiento más barato.
  • Ordenación: ORDER BY occurred_at DESC LIMIT 100 aprovecha el índice cuando la columna líder es occurred_at.

Errores comunes (y soluciones)

  • Límite 2038 (MySQL TIMESTAMP): usa DATETIME(3) o epoch BIGINT para fechas futuras.
  • Deriva de zona de sesión: fija time_zone='+00:00' (MySQL) o SET TIMEZONE='UTC'; (PostgreSQL) al iniciar la conexión.
  • Precisión mezclada: no guardes segundos y milisegundos en la misma columna; valida longitud al ingresar.
  • Filtros con funciones: WHERE DATE(occurred_at)=... deshabilita índices; precalcula occurred_at_date.
  • Sorpresas por DST en reportes: guarda en UTC; localiza en la capa de vista con zonas IANA reales.

Checklist de migración

  1. Congela escrituras o usa dual‑write.
  2. Agrega una nueva columna UTC (ej. occurred_at_utc TIMESTAMPTZ o occurred_at_ms BIGINT).
  3. Rellena con una conversión determinística; valida con comprobaciones puntuales.
  4. Agrega índices a la nueva columna y cambia las lecturas.
  5. Elimina la columna legacy tras monitoreo y verificación.

FAQ

  • ¿Debo guardar epoch o datetime? BIGINT epoch es ideal para interoperabilidad y precisión; TIMESTAMPTZ es mejor para cálculos y legibilidad.
  • ¿Qué precisión elegir? Logs/trazas: ms o más. Datos de negocio/analítica: segundos suelen bastar.
  • ¿Cómo evito que los usuarios envíen hora local? Normaliza en el borde: acepta con zona horaria, convierte a UTC y guarda solo UTC.
  • ¿Cómo valido timestamps entrantes? Acepta solo dígitos, detecta precisión por longitud y aplica rangos razonables (p. ej., 2000-01-01 a 2100-01-01). Ver Timestamp Precision Levels.

Herramientas y guías relacionadas