Types date/heure et tables STRICT

Les données qu'on utilise — perf_sales

Les dates sont stockées soit en chaînes, soit en nombres, et tu les lis ou les calcules avec des fonctions comme date() / strftime().

Dans la première moitié, tu utiliseras la colonne sale_date de la table de ventes perf_sales pour vérifier les trois formats de stockage et comment extraire des valeurs ; dans la seconde moitié, tu créeras toi-même une table STRICT pour voir ce qui se passe quand tu insères une valeur qui ne correspond pas au type déclaré.

Avant les exercices, jette un œil aux définitions de colonnes et à un échantillon de données de la table perf_sales utilisée dans cet article. (Quand tu l'exécutes correctement, l'explication apparaît.)

① Exécute PRAGMA table_info(perf_sales); pour vérifier les définitions de colonnes.

② Exécute SELECT * FROM perf_sales LIMIT 5; pour prévisualiser les 5 premières lignes. perf_sales a beaucoup de lignes, donc ajoute toujours LIMIT.

Les trois formats de stockage des dates — chaîne ISO / julianday / unixepoch

Les dates et heures peuvent être stockées dans trois formats principaux.

Quel que soit le format que tu stockes, tu peux le relire en 'YYYY-MM-DD' avec date() ou en 'YYYY-MM-DD HH:MM:SS' avec datetime().

Ce cours utilise la chaîne ISO par défaut parce qu'elle est lisible par l'humain et facile à comparer, et ne convertit en julianday / unixepoch qu'au besoin.

-- La même date dans 3 formats (exemple en lecture seule)
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;

-- Reconvertir chacun des 3 formats en date lisible
SELECT date(julianday('2024-03-15'))            AS from_jd,
       datetime(1710460800,'unixepoch')         AS from_unix,
       date('2024-03-15')                       AS from_iso;

-- Avec les chaînes ISO, la comparaison par plage fonctionne correctement en simple comparaison de chaînes
SELECT '2024-03-15' BETWEEN '2024-01-01' AND '2024-12-31' AS in_2024;  -- 1

Suppose que l'exigence soit : « Pour une vente de perf_sales, affiche sa date de vente côte à côte dans les formats chaîne ISO, julianday et unixepoch. »

① Réduis perf_sales à l'unique ligne où sale_id = 12345 (la table étant grande, limite-toi toujours à une ligne).

② Lis le sale_date de cette ligne tel quel sous l'alias iso.

③ À côté, sors julianday() appliqué au même sale_date en jd, et strftime('%s', ...) en unixep.

Manipuler et agréger les dates avec date() et strftime()

• date(valeur, modificateur...) : renvoie la partie date en 'YYYY-MM-DD', et te laisse faire de l'arithmétique de dates avec des modificateurs comme '+1 month' / '-7 days' / 'start of month'
• strftime(format, valeur) : extrait un morceau de la date sous forme de chaîne en utilisant des spécificateurs de format comme %Y (année), %m (mois), %d (jour), %w (jour de la semaine)
• Combine strftime('%Y', sale_date) avec GROUP BY pour agréger par année
• Comme sale_date est stockée en chaîne ISO, les conditions de plage comme `WHERE sale_date >= '2024-01-01'` fonctionnent correctement en simple comparaison de chaînes

-- Manipulation de dates (exemple en lecture seule)
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

-- Compter par année (chaîne ISO, donc extraire l'année avec strftime et GROUP BY)
SELECT strftime('%Y', sale_date) AS yr, COUNT(*) AS cnt
FROM perf_sales
GROUP BY yr
ORDER BY yr;

Suppose que l'exigence soit : « Groupe les ventes de perf_sales par année et affiche le nombre et le montant total pour chaque année. » Comme sale_date est une chaîne ISO, utilise strftime pour extraire l'année puis agrège.

① Extrais l'année de sale_date avec strftime('%Y', ...) et alias-la en yr.

② Sors le nombre de chaque année en cnt, et la somme d'amount en total.

③ Groupe par année (yr) et trie les résultats par année croissante (agrège toujours — la table a beaucoup de lignes).

Suppose que l'exigence soit : « Pour le seul T1 2024 (1er janvier au 31 mars), affiche le nombre et le montant moyen. » Comme sale_date est une chaîne ISO, les conditions de plage fonctionnent correctement en simple comparaison de chaînes.

① De perf_sales, ne garde que les lignes où sale_date est entre 2024-01-01 et 2024-03-31 (inclus).

② Sors le nombre de ces lignes en cnt et la moyenne d'amount en avg_amount.

③ Ne groupe pas — renvoie une seule ligne d'agrégat couvrant toute la période.

Tables STRICT — appliquer un typage strict

L'affinité de type qu'on a vue jusqu'ici convertit avec souplesse les chaînes qui ressemblent à des nombres.

À l'inverse, quand tu ajoutes STRICT à la fin d'une définition de table, cette table refuse de stocker des valeurs dont le type ne correspond pas à celui déclaré.

Si tu essaies d'insérer une chaîne non numérique dans une colonne numérique, la valeur n'est pas convertie implicitement — tu obtiens une erreur.

Utilise ça pour les tables où tu veux attraper tôt les confusions de types.

Dans une table STRICT, les types de colonnes autorisés sont limités à INTEGER / REAL / TEXT / BLOB / ANY — INT et les noms de types personnalisés ne sont pas autorisés.

Dans l'exercice suivant tu créeras toi-même une table typed_strict avec STRICT, et dans les exercices qui suivent tu insèreras des valeurs qui correspondent et qui ne correspondent pas pour voir la différence.

-- Déclaration et usage d'une table STRICT (exemple en lecture seule)
CREATE TABLE IF NOT EXISTS typed_strict(a INTEGER, b TEXT, c REAL) STRICT;

-- Les lignes avec des types qui correspondent s'insèrent sans souci
INSERT INTO typed_strict VALUES (1, '2024-03-15', 1.5);

-- Insérer une chaîne non numérique dans la colonne a INTEGER
-- n'est pas converti implicitement -- l'INSERT lève une erreur :
--   cannot store TEXT value in INTEGER column typed_strict.a
INSERT INTO typed_strict VALUES ('not-a-number', 'x', 1.0);

Suppose que l'exigence soit : « Définir moi-même une table avec STRICT pour pouvoir vérifier le comportement de la vérification de type dans les exercices suivants. » Avant les deux prochains exercices (où tu insèreras des lignes qui correspondent et qui ne correspondent pas), crée la table elle-même ici.

① Exécute d'abord DROP TABLE IF EXISTS typed_strict; pour nettoyer toute version précédente, puis CREATE une table typed_strict avec trois colonnes — a (INTEGER) / b (TEXT) / c (REAL) — en ajoutant STRICT à la fin.

② Exécute ensuite PRAGMA table_info(typed_strict); pour confirmer que les trois colonnes sont définies comme déclarées.

Suppose que l'exigence soit : « Ajouter une ligne à la table typed_strict de l'exercice 4 avec des valeurs qui correspondent au type déclaré de chaque colonne, et confirmer qu'elle est stockée. »

① Insère une ligne dans typed_strict (a INTEGER / b TEXT / c REAL) : un entier dans a, une chaîne de date au format ISO dans b, et un décimal dans c. Tu peux choisir librement les valeurs (utilise par exemple une chaîne ressemblant à une date dans b).

② Exécute ensuite SELECT * FROM typed_strict; pour confirmer que la ligne insérée y est.

Vérifions ce qui se passe quand tu essaies d'insérer une chaîne non numérique dans la colonne a (INTEGER) de typed_strict. Une table STRICT ne convertit pas les valeurs incompatibles — elle les rejette, donc cet INSERT va lever une erreur. C'est le dernier exercice de l'article, et le but est de vivre l'erreur elle-même.

① Lance un INSERT qui essaie de mettre une chaîne non numérique comme 'not-a-number' dans la colonne a de typed_strict.

② Confirme qu'un message d'erreur apparaît (dans cette console, avoir une erreur signifie que tu as bien fait).