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:


SQL
1CREATE TABLE events (
2  id BIGINT PRIMARY KEY AUTO_INCREMENT,
3  occurred_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
4  occurred_at_ms BIGINT GENERATED ALWAYS AS (UNIX_TIMESTAMP(occurred_at) * 1000) STORED,
5  INDEX idx_events_occurred_at (occurred_at)
6);

PostgreSQL

Preferido: TIMESTAMPTZ (almacena UTC, renderiza con zona).
Precisión: hasta microsegundos; soporta columnas GENERATED ALWAYS para epoch.


SQL
1CREATE TABLE events (
2  id BIGSERIAL PRIMARY KEY,
3  occurred_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
4  occurred_at_ms BIGINT GENERATED ALWAYS AS (EXTRACT(EPOCH FROM occurred_at) * 1000)::BIGINT STORED,
5  INDEX (occurred_at)
6);

SQLite

Almacena como TEXT, REAL o INTEGER; elige INTEGER epoch para consistencia.


SQL
1CREATE TABLE events (
2  id INTEGER PRIMARY KEY,
3  occurred_at_ms INTEGER NOT NULL, -- store UTC milliseconds
4  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
5);
6CREATE INDEX idx_events_occurred_at_ms ON events (occurred_at_ms);

Conversión epoch ↔ tiempo legible

MySQL


SQL
1-- epoch (seconds) -> timestamp
2SELECT FROM_UNIXTIME(1704067200) AS utc_time;
3-- timestamp -> epoch seconds/milliseconds
4SELECT UNIX_TIMESTAMP(occurred_at) AS epoch_s,
5       UNIX_TIMESTAMP(occurred_at) * 1000 AS epoch_ms
6FROM events;
7-- apply timezone safely
8SELECT CONVERT_TZ(occurred_at, 'UTC', 'America/New_York') FROM events;

PostgreSQL


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

SQLite


SQL
1-- epoch milliseconds -> UTC datetime string
2SELECT datetime(occurred_at_ms / 1000, 'unixepoch') AS utc_time FROM events;
3-- UTC datetime string -> epoch milliseconds
4SELECT 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

Congela escrituras o usa dual‑write.
Agrega una nueva columna UTC (ej. occurred_at_utc TIMESTAMPTZ o occurred_at_ms BIGINT).
Rellena con una conversión determinística; valida con comprobaciones puntuales.
Agrega índices a la nueva columna y cambia las lecturas.
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

Por qué esta página

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:


SQL
1CREATE TABLE events (
2  id BIGINT PRIMARY KEY AUTO_INCREMENT,
3  occurred_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
4  occurred_at_ms BIGINT GENERATED ALWAYS AS (UNIX_TIMESTAMP(occurred_at) * 1000) STORED,
5  INDEX idx_events_occurred_at (occurred_at)
6);

PostgreSQL

Preferido: TIMESTAMPTZ (almacena UTC, renderiza con zona).
Precisión: hasta microsegundos; soporta columnas GENERATED ALWAYS para epoch.


SQL
1CREATE TABLE events (
2  id BIGSERIAL PRIMARY KEY,
3  occurred_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
4  occurred_at_ms BIGINT GENERATED ALWAYS AS (EXTRACT(EPOCH FROM occurred_at) * 1000)::BIGINT STORED,
5  INDEX (occurred_at)
6);

SQLite

Almacena como TEXT, REAL o INTEGER; elige INTEGER epoch para consistencia.


SQL
1CREATE TABLE events (
2  id INTEGER PRIMARY KEY,
3  occurred_at_ms INTEGER NOT NULL, -- store UTC milliseconds
4  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
5);
6CREATE INDEX idx_events_occurred_at_ms ON events (occurred_at_ms);

Conversión epoch ↔ tiempo legible

MySQL


SQL
1-- epoch (seconds) -> timestamp
2SELECT FROM_UNIXTIME(1704067200) AS utc_time;
3-- timestamp -> epoch seconds/milliseconds
4SELECT UNIX_TIMESTAMP(occurred_at) AS epoch_s,
5       UNIX_TIMESTAMP(occurred_at) * 1000 AS epoch_ms
6FROM events;
7-- apply timezone safely
8SELECT CONVERT_TZ(occurred_at, 'UTC', 'America/New_York') FROM events;

PostgreSQL


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

SQLite


SQL
1-- epoch milliseconds -> UTC datetime string
2SELECT datetime(occurred_at_ms / 1000, 'unixepoch') AS utc_time FROM events;
3-- UTC datetime string -> epoch milliseconds
4SELECT 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

Congela escrituras o usa dual‑write.
Agrega una nueva columna UTC (ej. occurred_at_utc TIMESTAMPTZ o occurred_at_ms BIGINT).
Rellena con una conversión determinística; valida con comprobaciones puntuales.
Agrega índices a la nueva columna y cambia las lecturas.
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.