Tipos de fecha/hora y tablas STRICT

Los datos que usaremos — perf_sales

Las fechas se almacenan como cadenas o como números, y las lees o calculas con funciones como date() / strftime().

En la primera mitad, usarás la columna sale_date de la tabla de ventas perf_sales para revisar los tres formatos de almacenamiento y cómo extraer valores; en la segunda mitad, crearás tú mismo una tabla STRICT para ver qué pasa cuando insertas un valor que no coincide con el tipo declarado.

Antes de los ejercicios, echa un vistazo a las definiciones de columnas y a una muestra de datos de la tabla perf_sales usada en este artículo. (Cuando lo ejecutes correctamente, aparecerá la explicación.)

① Ejecuta PRAGMA table_info(perf_sales); para revisar las definiciones de columnas.

② Ejecuta SELECT * FROM perf_sales LIMIT 5; para previsualizar las 5 primeras filas. perf_sales tiene muchas filas, así que añade siempre LIMIT.

Los tres formatos de almacenamiento de fechas — cadena ISO / julianday / unixepoch

Las fechas y horas se pueden almacenar en tres formatos principales.

Sea cual sea el formato que almacenes, puedes leerlo de vuelta como 'YYYY-MM-DD' con date() o como 'YYYY-MM-DD HH:MM:SS' con datetime().

Este curso usa la cadena ISO por defecto porque es legible por humanos y fácil de comparar, y solo convierte a julianday / unixepoch cuando hace falta.

-- La misma fecha en 3 formatos (ejemplo de solo lectura)
SELECT '2024-03-15'                       AS iso_text,
       julianday('2024-03-15')            AS as_julianday,
       strftime('%s','2024-03-15 00:00:00') AS as_unixepoch;

-- Convierte cada uno de los 3 formatos de vuelta a una fecha legible
SELECT date(julianday('2024-03-15'))            AS from_jd,
       datetime(1710460800,'unixepoch')         AS from_unix,
       date('2024-03-15')                       AS from_iso;

-- Con cadenas ISO, la comparación por rango funciona correctamente como comparación de cadenas
SELECT '2024-03-15' BETWEEN '2024-01-01' AND '2024-12-31' AS in_2024;  -- 1

Supón el requisito: "Para una venta de perf_sales, muestra su fecha de venta lado a lado en los formatos cadena ISO, julianday y unixepoch".

① Reduce perf_sales a la única fila donde sale_id = 12345 (como la tabla es grande, limita siempre a una fila).

② Lee el sale_date de esa fila tal cual con el alias iso.

③ Al lado, devuelve julianday() aplicado al mismo sale_date como jd, y strftime('%s', ...) como unixep.

Manipula y agrega fechas con date() y strftime()

• date(value, modifier...): devuelve la parte de fecha como 'YYYY-MM-DD' y te permite hacer aritmética de fechas con modificadores como '+1 month' / '-7 days' / 'start of month'
• strftime(format, value): extrae una parte de la fecha como cadena usando especificadores de formato como %Y (año), %m (mes), %d (día), %w (día de la semana)
• Combina strftime('%Y', sale_date) con GROUP BY para agregar por año
• Como sale_date se almacena como cadena ISO, las condiciones de rango como `WHERE sale_date >= '2024-01-01'` funcionan correctamente como comparaciones de cadenas

-- Manipulación de fechas (ejemplo de solo lectura)
SELECT date('2024-03-15','+1 month')      AS plus_1_month,   -- 2024-04-15
       date('2024-03-15','start of month') AS month_start,    -- 2024-03-01
       strftime('%Y',   '2024-03-15')      AS year_part,      -- 2024
       strftime('%Y-%m','2024-03-15')      AS year_month;     -- 2024-03

-- Conteo por año (cadena ISO: extrae el año con strftime y GROUP BY)
SELECT strftime('%Y', sale_date) AS yr, COUNT(*) AS cnt
FROM perf_sales
GROUP BY yr
ORDER BY yr;

Supón el requisito: "Agrupa las ventas de perf_sales por año y muestra el conteo y el monto total para cada año". Como sale_date es una cadena ISO, usa strftime para extraer el año y luego agregar.

① Extrae el año de sale_date con strftime('%Y', ...) y dale el alias yr.

② Devuelve el conteo de cada año como cnt y la suma de amount como total.

③ Agrupa por año (yr) y ordena los resultados en orden ascendente de año (agrega siempre — la tabla tiene muchas filas).

Supón el requisito: "Solo para el primer trimestre de 2024 (del 1 de enero al 31 de marzo), muestra el conteo y el monto promedio". Como sale_date es una cadena ISO, las condiciones de rango funcionan correctamente como comparaciones de cadenas.

① De perf_sales, conserva solo las filas donde sale_date esté entre 2024-01-01 y 2024-03-31 (ambos inclusive).

② Devuelve el conteo de esas filas como cnt y el promedio de amount como avg_amount.

③ No agrupes — devuelve una sola fila agregada que cubra todo el periodo.

Tablas STRICT — imponer tipado estricto

La afinidad de tipos que hemos visto hasta ahora convierte flexiblemente las cadenas con apariencia numérica.

Por el contrario, cuando añades STRICT al final de una definición de tabla, esa tabla rechaza almacenar valores cuyo tipo no coincida con el declarado.

Si intentas insertar una cadena no numérica en una columna numérica, el valor no se convierte implícitamente — obtienes un error.

Úsalo para tablas en las que quieras detectar pronto las confusiones de tipos.

En una tabla STRICT, los tipos de columna permitidos se limitan a INTEGER / REAL / TEXT / BLOB / ANY — INT y los nombres de tipo personalizados no están permitidos.

En el próximo ejercicio crearás tú mismo una tabla typed_strict con STRICT, y en los ejercicios siguientes insertarás valores coincidentes y no coincidentes para ver la diferencia.

-- Declarar y usar una tabla STRICT (ejemplo de solo lectura)
CREATE TABLE IF NOT EXISTS typed_strict(a INTEGER, b TEXT, c REAL) STRICT;

-- Las filas con tipos coincidentes se insertan sin problemas
INSERT INTO typed_strict VALUES (1, '2024-03-15', 1.5);

-- Insertar una cadena no numérica en la columna a INTEGER
-- no se convierte implícitamente -- el INSERT da error:
--   cannot store TEXT value in INTEGER column typed_strict.a
INSERT INTO typed_strict VALUES ('not-a-number', 'x', 1.0);

Supón el requisito: "Definir una tabla con STRICT yo mismo para poder revisar el comportamiento de comprobación de tipos en los siguientes ejercicios". Antes de los dos próximos ejercicios (donde insertarás filas coincidentes y no coincidentes), crea aquí la tabla en sí.

① Primero ejecuta DROP TABLE IF EXISTS typed_strict; para limpiar cualquier versión anterior, luego haz CREATE de una tabla typed_strict con tres columnas — a (INTEGER) / b (TEXT) / c (REAL) — añadiendo STRICT al final.

② Luego ejecuta PRAGMA table_info(typed_strict); para confirmar que las tres columnas están definidas como se declararon.

Supón el requisito: "Añade una fila a la tabla typed_strict del Ejercicio 4 con valores que coincidan con el tipo declarado de cada columna y confirma que se almacena".

① Inserta una fila en typed_strict (a INTEGER / b TEXT / c REAL): un entero en a, una cadena de fecha en formato ISO en b y un decimal en c. Puedes elegir los valores libremente (usa por ejemplo una cadena con forma de fecha en b).

② Luego ejecuta SELECT * FROM typed_strict; para confirmar que la fila insertada está ahí.

Vamos a comprobar qué pasa cuando intentas insertar una cadena no numérica en la columna a (INTEGER) de typed_strict. Una tabla STRICT no convierte los valores no coincidentes — los rechaza, por lo que este INSERT dará error. Este es el último ejercicio del artículo y el punto es experimentar el error en sí.

① Ejecuta un INSERT que intente meter una cadena no numérica como 'not-a-number' en la columna a de typed_strict.

② Confirma que aparece un mensaje de error (en esta consola, obtener un error significa que lo has hecho bien).