Fechas en bases de datos: SQLite TEXT, INTEGER o REAL

Serie: curso de fechas y zonas horarias 5/6 (avanzado). Para quien ya escribe SQL y ahora tiene que decidir qué guarda de verdad una columna created_at. Al terminar vas a poder elegir un formato para el tiempo con datos medidos, detectar los tres errores que corrompen fechas en silencio y mantener honesta una API JSON con los offsets.

Si tienes una app de reservas o de facturación con clientes en México, Colombia, Argentina y España, ya viste alguno de estos reportes: "en el reporte del 10 de marzo faltan pedidos", "el pedido más nuevo no sale arriba", "todas las horas de la base están seis horas adelantadas". Los errores de tiempo en una base de datos casi nunca lanzan una excepción: la consulta corre, devuelve filas, y las filas están mal. En esta clase guardamos los mismos 1,000,000 de instantes de tres formas en SQLite, medimos tamaño y velocidad, y luego rompemos cosas a propósito: offsets mezclados, T contra espacio, fechas imposibles, un contador de 32 bits en C y un JSON cuyo significado depende de la zona del servidor.

Entorno: SQLite 3.51.0 (la CLI sqlite3 y el módulo sqlite3 de Python reportan 3.51.0), Python 3.9.6 con tzdata 2026d del sistema, Python 3.12 solo para una advertencia, Apple clang 21, jsc de JavaScriptCore, Mac con Apple Silicon (M1 Pro). Cada comando fija su zona (TZ=UTC, TZ=America/Mexico_City, etc.). Las salidas están pegadas tal cual y las rutas acortadas a ~/demo/. Si SQL es nuevo para ti, empieza por lo básico de SQL y índices y EXPLAIN QUERY PLAN.

SQLite no tiene tipo fecha: tres formas de guardar un instante

SQLite tiene cinco clases de almacenamiento (NULL, INTEGER, REAL, TEXT, BLOB) y ningún tipo fecha. Sus funciones de fecha aceptan tres formas de escribir el tiempo:

• TEXT: ISO 8601, como 2026-03-10 09:00:00 o 2026-03-10T09:00:00Z.
• INTEGER: segundos Unix (desde 1970-01-01 00:00:00 UTC), leídos con el modificador 'unixepoch'.
• REAL: número de día juliano, días desde el mediodía del 24 de noviembre del 4714 a. C. (gregoriano proléptico). 2026-03-01 00:00:00 es 2461100.5.

SELECT typeof('2026-03-10'), typeof(1773057600), typeof(julianday('2026-03-10'));
-- text|integer|real

Antes de medir, una distinción decide casi todo el diseño. Un instante es algo que pasó en un punto de la línea de tiempo global (se pagó un pedido). Una hora de reloj de pared es lo que marca un reloj en algún lugar (la tienda de Madrid abre a las 09:00). Todos los instantes se pueden guardar en UTC. Las horas de pared futuras no, porque todavía no se sabe qué offset va a regir ese día; lo vemos en la clase 6.

Medido: 1,000,000 de filas como TEXT, INTEGER y REAL

El benchmark: 1,000,000 de instantes al azar entre 2020 y 2029 (random.Random(42)), cada formato en su propio archivo SQLite: TEXT YYYY-MM-DD HH:MM:SS (UTC), INTEGER en segundos Unix o REAL en día juliano. Luego un índice en la columna, un conteo de un mes (ts >= ? AND ts < ?) con y sin índice, y un GROUP BY por día sobre todas las filas.

# bench.py (núcleo), Python 3.9.6, sqlite 3.51.0
kinds = {
    "text":    ("TEXT",    lambda e: datetime.fromtimestamp(e, timezone.utc).strftime("%Y-%m-%d %H:%M:%S")),
    "integer": ("INTEGER", lambda e: e),
    "real":    ("REAL",    lambda e: e / 86400.0 + 2440587.5),   # día juliano
}
day_expr = {"text": "substr(ts,1,10)", "integer": "date(ts,'unixepoch')", "real": "date(ts)"}
# ... CREATE TABLE ev(id INTEGER PRIMARY KEY, ts <tipo> NOT NULL); executemany; CREATE INDEX ev_ts ON ev(ts)

Salida (mínimo / mediana en ms; 20 corridas con índice, 5 sin índice, 3 para el group by):

text     insert  2.62s  file  28.1 MB ->  56.2 MB with index | month count idx 0.16/0.17 ms (8503)  no idx 51.3/52.0 ms | group by day 428/431 ms (3653 days)
   plan: SEARCH ev USING COVERING INDEX ev_ts (ts>? AND ts<?)
integer  insert  0.50s  file  13.1 MB ->  26.1 MB with index | month count idx 0.15/0.15 ms (8503)  no idx 37.5/37.7 ms | group by day 423/424 ms (3653 days)
   plan: SEARCH ev USING COVERING INDEX ev_ts (ts>? AND ts<?)
real     insert  0.55s  file  17.1 MB ->  34.2 MB with index | month count idx 0.17/0.17 ms (8503)  no idx 43.8/44.0 ms | group by day 440/447 ms (3653 days)

Tres sorpresas. Primera: con índice, el tipo casi no importa para consultas por rango: de 0.15 a 0.17 ms en los tres, unas 300 veces más rápido que el recorrido completo. Segunda: los 2.62 s del insert de texto no fueron culpa de SQLite. Con los valores ya formateados, los inserts tardaron 0.48 s (texto), 0.44 s (entero) y 0.44 s (real); los otros dos segundos fueron el strftime de Python. Tercera: REAL no perdió nada con precisión de un segundo: uniendo los archivos REAL e INTEGER por id, datetime(r.ts) <> datetime(n.ts,'unixepoch') dio 0 filas, strftime('%f') dio 0 filas y unixepoch(r.ts) <> n.ts dio 0 filas. El costo real de TEXT es el tamaño: más del doble que INTEGER, en la tabla y otra vez en el índice.

datetime('now') es UTC, diga lo que diga el servidor

Un reporte clásico desde un servidor en México: "todas las horas de la base están seis horas adelantadas". No están adelantadas. El 'now' de SQLite siempre es UTC; solo el modificador 'localtime' lee la zona del proceso. El mismo comando en cinco zonas (la columna 'now' es el momento en que lo corrimos, en UTC):

for tz in UTC Asia/Seoul America/New_York Europe/Madrid America/Mexico_City; do
  TZ=$tz sqlite3 :memory: "SELECT datetime('now'), datetime(1773057600,'unixepoch'),
                                  datetime(1773057600,'unixepoch','localtime');"
done

UTC                2026-11-15 10:13:33|2026-03-09 12:00:00|2026-03-09 12:00:00
Asia/Seoul         2026-11-15 10:13:33|2026-03-09 12:00:00|2026-03-09 21:00:00
America/New_York   2026-11-15 10:13:33|2026-03-09 12:00:00|2026-03-09 08:00:00
Europe/Madrid      2026-11-15 10:13:33|2026-03-09 12:00:00|2026-03-09 13:00:00
America/Mexico_City 2026-11-15 10:13:33|2026-03-09 12:00:00|2026-03-09 06:00:00

En Ciudad de México las 12:00 UTC son las 06:00: seis horas exactas, todo el año, porque México dejó el horario de verano (la base tz no tiene transiciones para America/Mexico_City después de octubre de 2022). Nueva York marca 08:00 y no 07:00 porque el horario de verano de EE. UU. empezó el 8 de marzo de 2026 (clase 2). Y Madrid sigue en 13:00 (CET) porque en Europa el cambio llega el último domingo de marzo. El modificador 'localtime' sabía todo eso porque usa la base de datos tz.

Dos reglas: guarda datetime('now') tal cual y convierte al mostrar, y nunca metas 'localtime' en los datos guardados. SQLite hace cumplir una parte por sí mismo. Un índice de expresión sobre datetime(at,'localtime') falla en cuanto el índice tiene que guardar una fila, sin importar si la fila se insertó antes o después del CREATE INDEX:

Error: stepping, non-deterministic use of datetime() in an index

Un índice sobre unixepoch(at) sí funciona: EXPLAIN QUERY PLAN mostró SEARCH ev USING COVERING INDEX ev_at_epoch (<expr>>? AND <expr><?).

Error común: texto ISO con offsets mezclados

El texto ISO 8601 se ordena bien solo si todos los valores tienen el mismo formato y el mismo offset. Los equipos lo rompen cuando cada cliente manda su hora local y el servidor guarda la cadena sin tocarla, algo muy típico en apps de freelance con usuarios repartidos por Latinoamérica y España:

CREATE TABLE b(id INTEGER PRIMARY KEY, at TEXT);
INSERT INTO b(at) VALUES
 ('2026-03-10T09:00:00+09:00'),   -- Tokio, 00:00 UTC
 ('2026-03-09T20:00:00-05:00'),   -- Nueva York, 01:00 UTC del día 10
 ('2026-03-10T01:30:00+01:00'),   -- Madrid, 00:30 UTC
 ('2026-03-09T23:45:00Z');        -- UTC
SELECT id, at FROM b ORDER BY at;
SELECT id, at, datetime(at), unixepoch(at) FROM b ORDER BY unixepoch(at);

text order:
2|2026-03-09T20:00:00-05:00
4|2026-03-09T23:45:00Z
3|2026-03-10T01:30:00+01:00
1|2026-03-10T09:00:00+09:00
instant order:
4|2026-03-09T23:45:00Z|2026-03-09 23:45:00|1773099900
1|2026-03-10T09:00:00+09:00|2026-03-10 00:00:00|1773100800
3|2026-03-10T01:30:00+01:00|2026-03-10 00:30:00|1773102600
2|2026-03-09T20:00:00-05:00|2026-03-10 01:00:00|1773104400

Orden de texto: 2, 4, 3, 1. Orden real: 4, 1, 3, 2. La fila de Nueva York, que ocurrió al final, queda primero. A escala es peor: 10,000 eventos de marzo de 2026 con offsets al azar de cuatro zonas (semilla 42):

rows at the same position in both orders: 93
adjacent pairs out of order in text order: 3723 of 9999
'March 10 UTC' as text range: 331 rows, true: 335, overlap: 283

La consulta de texto para "10 de marzo UTC" devolvió 331 filas, y solo 283 eran correctas: se colaron 48 filas de otros días y faltaron 52 de verdad. El total se ve razonable, por eso nadie lo nota.

Arreglo: convierte a UTC antes del INSERT (datetime(?) normaliza cualquier offset a texto UTC, o guarda unixepoch(?)). Si ya tienes datos con offsets mezclados, ordena y filtra por unixepoch(at) con un índice de expresión sobre eso.

Dos trampas de texto más, de la misma sesión:

SELECT '2026-03-10 09:00:00' < '2026-03-10T08:00:00';   -- 1  (el espacio va antes que 'T')
SELECT quote(datetime('2026-02-30 10:00:00'));          -- '2026-03-02 10:00:00'
SELECT quote(date('2026-02-29'));                       -- '2026-03-01'  (2026 no es bisiesto)
SELECT quote(datetime('2026-13-01')), quote(datetime('2026/03/10 10:00'));  -- NULL|NULL

La primera aparece al mezclar tus cadenas con T y la salida con espacio de datetime(): las 09:00 quedan "antes" que las 08:00. La segunda es normalización silenciosa: SQLite convierte el 30 de febrero en 2 de marzo en lugar de fallar, el mismo desborde que viste con PHP en la clase 3. La tercera: un formato desconocido da NULL, así que una restricción NOT NULL es un guardia barato.

Tu driver también opina: sqlite3 de Python

El módulo sqlite3 de Python puede convertir objetos datetime por ti. Con detect_types=PARSE_DECLTYPES y una columna declarada TIMESTAMP, un valor naive y uno aware se guardaron bien, pero al leerlos falló el aware:

3.9.6
[('2026-03-10 09:00:00',), ('2026-03-10 09:00:00+09:00',)]
ValueError: invalid literal for int() with base 10: b'00+09'

En Python 3.12 el mismo script primero imprime esto y luego falla con el mismo ValueError:

~/demo/adapt.py:8: DeprecationWarning: The default datetime adapter is deprecated as of Python 3.12; see the sqlite3 documentation for suggested replacement recipes
~/demo/adapt.py:12: DeprecationWarning: The default timestamp converter is deprecated as of Python 3.12; see the sqlite3 documentation for suggested replacement recipes

Haz la conversión tú: int(dt.timestamp()) al guardar, datetime.fromtimestamp(x, timezone.utc) al leer. Así tu código hace lo mismo en cualquier versión de Python.

MySQL DATETIME vs TIMESTAMP y otras bases (no corrido aquí)

No levantamos un servidor MySQL ni PostgreSQL para esta clase; lo que sigue es el comportamiento documentado.

En un hosting compartido donde no puedes cambiar time_zone, las columnas TIMESTAMP son una fuente clásica de "las horas cambiaron después de migrar". Fijar la zona de la sesión en UTC (SET time_zone = '+00:00') al abrir la conexión hace que la conversión no haga nada. Otros límites de MySQL en hosting compartido: max_connections_per_hour en hosting compartido.

Y2K38: un contador de 32 bits en C, medido

Los segundos Unix en un entero con signo de 32 bits terminan en 2,147,483,647, que es 2038-01-19 03:14:07 UTC. En esta Mac time_t es de 64 bits, así que forzamos el caso de 32 bits con int32_t:

/* y2k38.c */
#include <inttypes.h>
#include <stdint.h>
#include <stdio.h>
#include <time.h>

static void show(const char *label, time_t t) {
    char buf[32];
    struct tm tm;
    gmtime_r(&t, &tm);
    strftime(buf, sizeof buf, "%Y-%m-%d %H:%M:%S", &tm);
    printf("%-22s %12lld  %s UTC\n", label, (long long)t, buf);
}

int main(void) {
    printf("sizeof(time_t) = %zu bytes\n", sizeof(time_t));
    int32_t last = INT32_MAX;                         /* 2147483647 */
    show("int32 max", last);
    show("time_t max+1 (64-bit)", (time_t)last + 1);
    int32_t wrapped = (int32_t)((uint32_t)last + 1u); /* lo que hace un contador de 32 bits */
    show("int32 after +1 (wrap)", wrapped);
    int32_t ub = last;
    ub = ub + 1;                                      /* desborde con signo: comportamiento indefinido */
    show("int32 + 1 (UB)", ub);
    return 0;
}

$ clang -std=c17 -Wall -Wextra -O0 y2k38.c -o y2k38 && ./y2k38
sizeof(time_t) = 8 bytes
int32 max                2147483647  2038-01-19 03:14:07 UTC
time_t max+1 (64-bit)    2147483648  2038-01-19 03:14:08 UTC
int32 after +1 (wrap)   -2147483648  1901-12-13 20:45:52 UTC
int32 + 1 (UB)          -2147483648  1901-12-13 20:45:52 UTC

Un segundo después de 2038-01-19 03:14:07, un contador de 32 bits salta a 1901-12-13 20:45:52. El compilador no avisó nada sobre ub + 1; solo UBSan (-fsanitize=undefined) lo atrapó en tiempo de ejecución:

y2k38.c:22:13: runtime error: signed integer overflow: 2147483647 + 1 cannot be represented in type 'int'

Por qué es "indefinido" y no simplemente "da la vuelta" se explica en comportamiento indefinido y optimización (en inglés). Dónde sigue escondido el tiempo de 32 bits en 2026: formatos binarios y protocolos de red con campos de 4 bytes, TIMESTAMP de MySQL, firmware embebido viejo y código que mete el tiempo en un int de 32 bits. El struct de Python lo rechaza de frente: struct.error: 'i' format requires -2147483648 <= number <= 2147483647. SQLite no tiene ese límite: datetime(2147483648,'unixepoch') imprimió 2038-01-19 03:14:08.

APIs JSON: manda ISO 8601 con offset, recibe con sospecha

JSON no tiene tipo fecha, así que cada API elige una convención. La que sobrevive a los cambios de zona es ISO 8601 / RFC 3339 con offset explícito, idealmente UTC con Z. Lo que hicieron las herramientas:

# Python 3.9.6
TypeError: Object of type datetime is not JSON serializable
{"at": "2026-03-10T09:00:00+09:00"} {"at": "2026-03-10T00:00:00Z"}
ValueError: Invalid isoformat string: '2026-03-10T00:00:00Z'      # fromisoformat en 3.9
2026-03-10 00:00:00+00:00                                          # la misma llamada en 3.12

json.dumps rechaza datetime: tú eliges el formato, y eso es bueno. El fromisoformat de Python 3.9 no entiende la Z que produce JavaScript; Python 3.11 la agregó (la vimos funcionar en 3.12). JavaScript en dos zonas explícitas:

const d = new Date("2026-03-10T09:00:00+09:00");
print(JSON.stringify({ at: d }));
print(JSON.stringify({ at: new Date("2026-03-10T09:00:00") }));   // ¡sin offset!
print(JSON.stringify({ at: new Date("not a date") }));

TZ=UTC
{"at":"2026-03-10T00:00:00.000Z"}
{"at":"2026-03-10T09:00:00.000Z"}
{"at":null}
TZ=America/Mexico_City
{"at":"2026-03-10T00:00:00.000Z"}
{"at":"2026-03-10T15:00:00.000Z"}
{"at":null}

La cadena con offset significó el mismo instante en todas partes. La cadena sin offset se leyó como hora local, así que el mismo código mandó dos instantes distintos, seis horas aparte, según la zona del servidor: justo lo que pasa cuando tu laptop está en Ciudad de México y producción en UTC. Y una fecha inválida se volvió null sin excepción, así que valida antes de serializar. Los encabezados HTTP usan otra convención (Date: ... GMT), que ves en caché HTTP.

Reglas para el contrato de tu API:

• Instantes: "2026-03-10T00:00:00Z", o segundos epoch en un campo cuyo nombre lo diga (created_at_unix). Nunca números epoch sin unidad; la clase 1 mostró qué pasa al leer milisegundos como segundos.
• Fechas sin hora (cumpleaños, vencimientos): "2026-03-10", sin zona ni medianoche.
• Eventos locales futuros: la hora local más un nombre de zona IANA, {"local": "2026-03-10T09:00", "zone": "America/Bogota"}.
• Rechaza en la entrada de la API las cadenas sin offset en lugar de adivinar.

Límites honestos y notas de versión

• Medido solo en SQLite 3.51.0 sobre una Mac con Apple Silicon (M1 Pro), una corrida del benchmark; tus números absolutos van a variar, las proporciones (el doble de tamaño, la misma velocidad con índice) no deberían. unixepoch() llegó en SQLite 3.38.0 y el modificador 'subsec' en 3.42.0; en un SQLite más viejo usa strftime('%s', ...).
• El comportamiento de MySQL y PostgreSQL está descrito según su documentación, no corrido aquí.
• Los adaptadores por defecto de sqlite3 en Python están obsoletos desde 3.12 (advertencia de arriba). datetime.fromisoformat acepta Z desde Python 3.11.
• time_t es de 64 bits en este macOS; el caso de 32 bits se forzó con int32_t. Los sistemas Linux de 32 bits pasaron a time_t de 64 bits en distintos momentos; no corrimos ninguno.
• Los días julianos REAL hicieron el viaje de ida y vuelta exacto con precisión de un segundo en nuestro millón de filas. No probamos microsegundos; si los necesitas, INTEGER en microsegundos es la opción segura.

Ejercicio

1. Una tabla orders(created TEXT) tiene valores como 2026-03-10T09:00:00-05:00 y 2026-03-09 20:00:00 (sin offset, escritos por un servidor viejo que corría en Ciudad de México). Escribe las dos primeras cosas que haces antes de tocar cualquier consulta.

Respuestas

1. Primero, averigua qué significa cada formato: cuenta filas por patrón (length(created), substr(created, 20)) y confirma, con logs o código, qué zona escribió las filas sin offset. Segundo, agrega una columna INTEGER nueva y llénala: unixepoch(created) para las filas con offset, y para las de Ciudad de México convierte en el código de la aplicación con ZoneInfo("America/Mexico_City") (SQLite no puede aplicar una zona con nombre distinta a la del proceso; y ojo, antes de octubre de 2022 esa zona sí tenía horario de verano, así que un offset fijo de -06:00 estaría mal para datos viejos). Solo entonces cambia las lecturas a la columna nueva y ponle un índice.

Anterior: Fechas y zonas horarias 4: aritmética de fechas, meses, bisiestos y duraciones · Siguiente: Fechas y zonas horarias 6: programar tareas, cron, zonas horarias y desfase de relojes · Volver a la ruta de estudio para tu primer trabajo.