Fechas y horas en SQLite: strftime, date() y datetime()

SQLite no tiene un tipo de dato para fechas

Esto descoloca a todo el que viene de Postgres o MySQL. SQLite maneja cinco clases de almacenamiento — NULL, INTEGER, REAL, TEXT y BLOB — y nada más. No existe DATE, ni DATETIME, ni TIMESTAMP. Puedes escribir created_at DATETIME en tu CREATE TABLE y SQLite lo aceptará sin protestar, pero el valor se guarda como texto o como número.

Lo que sí te ofrece SQLite es un conjunto de funciones que entienden tres formatos convencionales para trabajar con fechas:

• Texto ISO 8601 — '2026-04-23', '2026-04-23 10:15:00', '2026-04-23T10:15:00.123Z'.
• Timestamp Unix — segundos desde el 1 de enero de 1970 UTC, almacenado como entero.
• Día juliano — días fraccionarios desde el 4714 a. C., almacenado como real.

Elige uno y mantente fiel a esa decisión. El texto ISO 8601 es el más legible y, al ser una cadena, se ordena correctamente sin trucos; por eso es el formato por defecto.

SELECT date('now'),
       datetime('now'),
       strftime('%s', 'now'),
       julianday('now');

Cuatro formas de pedir "ahora mismo": fecha en texto, fecha-hora en texto, segundos Unix y día juliano. Todas describen el mismo instante.

Las cinco funciones de fecha

SQLite trae cinco funciones integradas que cubren casi todo lo que vas a necesitar al trabajar con fechas:

• date(time, ...) — devuelve YYYY-MM-DD.
• time(time, ...) — devuelve HH:MM:SS.
• datetime(time, ...) — devuelve YYYY-MM-DD HH:MM:SS.
• julianday(time, ...) — devuelve un número real (ideal para calcular diferencias).
• strftime(format, time, ...) — devuelve una cadena con el formato que tú definas.

Cada una recibe un valor de tiempo como primer argumento y, después, cualquier cantidad de modificadores en forma de cadena.

SELECT date('2026-04-23 10:15:00')          AS just_date,
       time('2026-04-23 10:15:00')          AS just_time,
       datetime(1745402100, 'unixepoch')    AS from_unix,
       julianday('2026-04-23')              AS julian;

Fíjate en 'unixepoch': así le indicas a las funciones de fecha que el entero de entrada es un timestamp Unix y no un día juliano. Si lo omites, SQLite asume que el número es juliano.

strftime: formatos de fecha personalizados en SQLite

strftime es la navaja suiza para dar formato a fechas en SQLite. Usa los mismos códigos con % que ya conoces de C o Python:

SELECT strftime('%Y-%m-%d',           'now') AS fecha_iso,
       strftime('%d/%m/%Y',           'now') AS europea,
       strftime('%H:%M',              'now') AS horas_minutos,
       strftime('%Y-%m-%dT%H:%M:%fZ', 'now') AS iso_con_ms,
       strftime('%w',                 'now') AS dia_de_la_semana,
       strftime('%j',                 'now') AS dia_del_anio;

Los códigos que vas a usar todo el tiempo:

• %Y — año con cuatro dígitos.
• %m — mes (01-12).
• %d — día del mes (01-31).
• %H, %M, %S — horas, minutos y segundos.
• %w — día de la semana (0=domingo).
• %j — día del año (001-366).
• %s — timestamp Unix.
• %f — segundos con fracción (SS.SSS).

strftime también es la forma de extraer partes de una fecha en SQLite: aquí no existen funciones tipo EXTRACT ni YEAR(). Simplemente formateas hasta quedarte con la parte que te interesa y, si necesitas un número, haces el cast correspondiente:

SELECT strftime('%Y', '2026-04-23') AS year_text,
       CAST(strftime('%Y', '2026-04-23') AS INTEGER) AS year_num,
       CAST(strftime('%m', '2026-04-23') AS INTEGER) AS month_num;

strftime siempre devuelve texto, así que envuélvelo en CAST(... AS INTEGER) cuando necesites operar o comparar como números.

Modificadores: aritmética de fechas sin operadores

Esta es la característica que hace que trabajar con fechas en SQLite sea agradable. Después del argumento de tiempo, puedes pasar todos los modificadores que quieras (en forma de cadenas), y se aplican en orden:

SELECT date('2026-04-23', '+7 days')              AS una_semana_despues,
       date('2026-04-23', '-1 month')             AS un_mes_antes,
       datetime('now', '+2 hours', '+30 minutes') AS en_dos_treinta,
       date('now', 'start of month')              AS primero_del_mes,
       date('now', 'start of month', '+1 month', '-1 day') AS ultimo_del_mes,
       date('now', 'weekday 0')                   AS proximo_domingo;

Los modificadores que vas a usar todo el tiempo:

• '+N days', '-N days', y lo mismo con hours, minutes, seconds, months, years.
• 'start of day', 'start of month', 'start of year' — trunca al inicio de ese período.
• 'weekday N' — salta al siguiente día de la semana indicado (0=domingo).
• 'localtime' y 'utc' — para convertir entre zonas horarias.

El truco del "último día del mes" merece la pena memorizarlo: inicio de mes, sumas un mes y restas un día. SQLite no trae una función LAST_DAY, pero encadenando modificadores consigues exactamente lo mismo.

UTC vs. hora local en SQLite

'now' siempre devuelve la hora en UTC. Si quieres la hora local, tienes que pedirla de forma explícita:

SELECT datetime('now')              AS utc_now,
       datetime('now', 'localtime') AS local_now,
       datetime('2026-04-23 10:00:00', 'utc') AS treated_as_local_then_utc;

El modificador 'localtime' convierte un valor en UTC a la zona horaria local del sistema. El modificador 'utc' hace lo contrario: interpreta la entrada como hora local y la pasa a UTC.

La buena costumbre es esta: guarda todo en UTC y convierte a hora local solo al mostrarlo. Mezclar zonas horarias en el almacenamiento provoca bugs que aparecen dos veces al año, cuando cambia el horario de verano.

Comparar fechas en SQLite y filtrar rangos

Si guardas las fechas como texto en formato ISO 8601, las comparaciones y el BETWEEN funcionan sin más: el ISO 8601 ordena igual lexicográficamente que cronológicamente. Por eso es el formato por defecto.

CREATE TABLE orders (
    id         INTEGER PRIMARY KEY,
    customer   TEXT,
    placed_at  TEXT NOT NULL  -- ISO 8601
);

INSERT INTO orders (customer, placed_at) VALUES
    ('Ada',   '2026-04-01 09:30:00'),
    ('Boris', '2026-04-15 14:00:00'),
    ('Carla', '2026-04-23 11:45:00'),
    ('Dmitri','2026-05-02 08:15:00');

SELECT customer, placed_at
FROM orders
WHERE placed_at >= '2026-04-01'
  AND placed_at <  '2026-05-01'
ORDER BY placed_at;

El rango semiabierto (>= inicio, < fin) es una costumbre muy útil: te ahorra de un plumazo la duda de "¿entró la medianoche del día 30 o no?".

Para los "últimos 7 días", deja que SQLite calcule el límite por ti:

CREATE TABLE orders (
    id         INTEGER PRIMARY KEY,
    customer   TEXT,
    placed_at  TEXT NOT NULL
);

INSERT INTO orders (customer, placed_at) VALUES
    ('Ada',   datetime('now', '-2 days')),
    ('Boris', datetime('now', '-10 days')),
    ('Carla', datetime('now', '-1 day')),
    ('Dmitri',datetime('now', '-30 days'));

SELECT customer, placed_at
FROM orders
WHERE placed_at >= datetime('now', '-7 days')
ORDER BY placed_at DESC;

Diferencia entre fechas

SQLite no tiene DATEDIFF. Con estos dos patrones cubres prácticamente cualquier caso para calcular la diferencia entre dos fechas en SQLite:

SELECT julianday('2026-04-23') - julianday('2026-04-01')           AS dias_entre,
       (julianday('2026-04-23 18:00') - julianday('2026-04-23 09:00')) * 24 AS horas_entre,
       strftime('%s', '2026-04-23 18:00') - strftime('%s', '2026-04-23 09:00') AS segundos_entre;

Las diferencias con julianday() se expresan en días (con precisión decimal), así que al multiplicar por 24 obtienes horas, y por 1440, minutos. Las diferencias con strftime('%s', ...) van en segundos, lo cual viene muy bien cuando necesitas un valor entero.

CAST(... AS INTEGER) trunca la parte decimal de los días si solo te interesan días completos:

CREATE TABLE orders (
    id         INTEGER PRIMARY KEY,
    customer   TEXT,
    placed_at  TEXT NOT NULL
);

INSERT INTO orders (customer, placed_at) VALUES
    ('Ada',   datetime('now', '-2 days')),
    ('Boris', datetime('now', '-10 days')),
    ('Carla', datetime('now', '-1 day')),
    ('Dmitri',datetime('now', '-30 days'));

SELECT customer,
       CAST(julianday('now') - julianday(placed_at) AS INTEGER) AS days_ago
FROM orders
ORDER BY days_ago;

Cómo guardar fechas en SQLite: elige un formato y respétalo

Tienes tres opciones razonables, ordenadas según cuál deberías usar primero:

1. Texto ISO 8601 (TEXT). Se lee bien en los volcados, ordena correctamente y se lleva de maravilla con todas las funciones de fecha. Es la opción por defecto.
2. Segundos Unix (INTEGER). Compacto, comparaciones rápidas y sin líos de zona horaria. Útil cuando tienes millones de filas. Eso sí, vas a necesitar datetime(col, 'unixepoch') para leerlo de vuelta.
3. Día juliano (REAL). Pocas veces compensa, salvo que estés haciendo aritmética intensiva con fechas y necesites precisión por debajo del segundo en una sola columna.

Lo que no debes hacer es mezclar formatos en una misma columna. Las funciones de fecha aceptan cualquiera sin quejarse, pero los índices, las ordenaciones y las comparaciones te van a devolver resultados sin sentido.

-- Valores por defecto que se autocompletan al insertar:
CREATE TABLE events (
    id          INTEGER PRIMARY KEY,
    name        TEXT NOT NULL,
    happened_at TEXT NOT NULL DEFAULT (datetime('now'))
);

INSERT INTO events (name) VALUES ('registro'), ('inicio de sesión');

SELECT name, happened_at FROM events;

DEFAULT (datetime('now')) es el equivalente en SQLite a DEFAULT CURRENT_TIMESTAMP: marca cada fila nueva con la hora UTC actual sin que tengas que tocar nada en el código de la aplicación.

Agrupar por periodos de tiempo

strftime brilla cuando necesitas agrupar filas por mes, semana u hora:

CREATE TABLE orders (
    id         INTEGER PRIMARY KEY,
    customer   TEXT,
    placed_at  TEXT NOT NULL
);

INSERT INTO orders (customer, placed_at) VALUES
    ('Ada',   '2026-04-01 09:30:00'),
    ('Boris', '2026-04-15 14:00:00'),
    ('Carla', '2026-04-23 11:45:00'),
    ('Dmitri','2026-05-02 08:15:00'),
    ('Ezra',  '2026-05-20 17:00:00');

SELECT strftime('%Y-%m', placed_at) AS month,
       COUNT(*)                     AS order_count
FROM orders
GROUP BY month
ORDER BY month;

La misma idea sirve para «pedidos por hora del día», «registros por día de la semana» o «eventos por minuto»: eliges una cadena de formato que capture justo la granularidad que necesitas, agrupas por ella y agregas.

Lo que viene: funciones de agregación

Hablando de agrupar, ese COUNT(*) es la más sencilla de las funciones de agregación de SQLite. A continuación veremos el conjunto completo: SUM, AVG, MIN, MAX, y cómo condensan muchas filas en un único valor resumen.