Dates en SQLite : strftime, date(), datetime() et modificateurs

SQLite n'a pas vraiment de type date

C'est le piège classique quand on arrive de Postgres ou MySQL. SQLite ne connaît que cinq classes de stockage — NULL, INTEGER, REAL, TEXT, BLOB — et c'est tout. Pas de DATE, pas de DATETIME, pas de TIMESTAMP. Vous pouvez très bien écrire created_at DATETIME dans votre CREATE TABLE : SQLite l'acceptera sans broncher, mais la valeur finira stockée sous forme de texte ou de nombre.

En revanche, SQLite fournit toute une série de fonctions qui savent manipuler trois formats conventionnels :

• Texte ISO 8601 — '2026-04-23', '2026-04-23 10:15:00', '2026-04-23T10:15:00.123Z'.
• Timestamp Unix — nombre de secondes depuis le 1er janvier 1970 UTC, stocké en entier.
• Jour julien — nombre de jours fractionnaires depuis 4714 av. J.-C., stocké en réel.

Choisissez-en un et tenez-vous-y. Le texte ISO 8601 reste le plus lisible et se trie correctement en tant que chaîne : c'est pour ça qu'il fait office de format par défaut.

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

Quatre manières de demander « maintenant » — date en texte, datetime en texte, secondes Unix, jour julien. Toutes désignent le même instant.

Les cinq fonctions de date

SQLite met à disposition cinq fonctions natives qui couvrent à peu près tous les besoins :

• date(time, ...) — renvoie YYYY-MM-DD.
• time(time, ...) — renvoie HH:MM:SS.
• datetime(time, ...) — renvoie YYYY-MM-DD HH:MM:SS.
• julianday(time, ...) — renvoie un réel (parfait pour calculer des écarts).
• strftime(format, time, ...) — renvoie une chaîne au format de votre choix.

Chacune prend une valeur temporelle en premier argument, suivie d'autant de modificateurs que vous voulez.

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;

À noter : 'unixepoch'. C'est le modificateur qui indique aux fonctions de date que l'entier passé est un timestamp Unix, et non un jour julien. Sans ce modificateur, SQLite considère par défaut que le nombre est un jour julien.

strftime : formater une date sur mesure

strftime est la fonction à tout faire pour le format de date sous SQLite. On retrouve les mêmes codes % qu'en C ou en Python :

SELECT strftime('%Y-%m-%d',           'now') AS date_iso,
       strftime('%d/%m/%Y',           'now') AS europeen,
       strftime('%H:%M',              'now') AS heures_minutes,
       strftime('%Y-%m-%dT%H:%M:%fZ', 'now') AS iso_avec_ms,
       strftime('%w',                 'now') AS jour_de_semaine,
       strftime('%j',                 'now') AS jour_de_annee;

Les codes que vous utiliserez le plus souvent :

• %Y — année sur quatre chiffres.
• %m — mois (01-12).
• %d — jour du mois (01-31).
• %H, %M, %S — heures, minutes, secondes.
• %w — jour de la semaine (0=dimanche).
• %j — jour de l'année (001-366).
• %s — timestamp Unix.
• %f — secondes fractionnaires (SS.SSS).

strftime sert aussi à extraire des morceaux d'une date — il n'existe pas de fonction EXTRACT ou YEAR() dédiée en SQLite. Il suffit de formater la date jusqu'à la partie qui vous intéresse, puis de la convertir en nombre si besoin :

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 renvoie toujours du texte. Si vous voulez faire des calculs ou des comparaisons numériques, pense à l'envelopper dans CAST(... AS INTEGER).

Modificateurs : du calcul de dates sans opérateur

C'est cette fonctionnalité qui rend la gestion des dates en SQLite vraiment agréable. Après l'argument temporel, vous pouvez enchaîner autant de chaînes modificatrices que vous voulez : elles s'appliquent dans l'ordre où vous les écrivez.

SELECT date('2026-04-23', '+7 days')              AS semaine_plus_tard,
       date('2026-04-23', '-1 month')             AS mois_anterieur,
       datetime('now', '+2 hours', '+30 minutes') AS dans_deux_heures_trente,
       date('now', 'start of month')              AS premier_du_mois,
       date('now', 'start of month', '+1 month', '-1 day') AS dernier_du_mois,
       date('now', 'weekday 0')                   AS prochain_dimanche;

Les modificateurs que vous utiliserez en permanence :

• '+N days', '-N days', et leurs équivalents pour hours, minutes, seconds, months, years.
• 'start of day', 'start of month', 'start of year' — pour tronquer à la borne correspondante.
• 'weekday N' — saute au prochain jour de la semaine indiqué (0 = dimanche).
• 'localtime' et 'utc' — pour passer d'un fuseau à l'autre.

L'astuce du « dernier jour du mois » — début du mois, plus un mois, moins un jour — vaut le coup d'être retenue par cœur. SQLite n'a pas de fonction LAST_DAY, mais en chaînant les modificateurs on obtient exactement le même résultat.

UTC ou heure locale dans SQLite

'now' renvoie toujours l'heure UTC. Si vous voulez l'heure locale, il faut le demander explicitement :

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;

Le modificateur 'localtime' convertit une valeur UTC vers le fuseau local du système. À l'inverse, 'utc' part du principe que l'entrée est en heure locale et la convertit en UTC.

La règle d'or : stocker en UTC partout, et ne convertir en heure locale qu'au moment de l'affichage. Mélanger les fuseaux dans la base, c'est s'exposer à des bugs qui ressortent pile deux fois par an, quand l'heure d'été ou d'hiver change.

Comparer des dates et filtrer des intervalles en SQLite

Si vous stockez vos dates au format texte ISO 8601, les comparaisons et le BETWEEN fonctionnent tout seuls : l'ordre lexicographique de l'ISO 8601 correspond exactement à l'ordre chronologique. C'est précisément pour ça que c'est le format par défaut.

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;

L'intervalle semi-ouvert (>= début, < fin) est un bon réflexe à prendre — ça évite tout de suite la question piège du genre « est-ce que minuit le 30 est compté ou pas ? ».

Pour récupérer « les 7 derniers jours », laissez SQLite calculer la borne pour vous :

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;

Différences entre deux dates

SQLite n'a pas de DATEDIFF. Mais avec deux astuces, on couvre tous les cas :

SELECT julianday('2026-04-23') - julianday('2026-04-01')           AS days_between,
       (julianday('2026-04-23 18:00') - julianday('2026-04-23 09:00')) * 24 AS hours_between,
       strftime('%s', '2026-04-23 18:00') - strftime('%s', '2026-04-23 09:00') AS seconds_between;

Les écarts calculés avec julianday() sont exprimés en jours (avec une précision décimale). Il suffit donc de multiplier par 24 pour obtenir des heures, ou par 1440 pour des minutes. Avec strftime('%s', ...), les différences sont en secondes — pratique quand on veut un entier directement.

CAST(... AS INTEGER) tronque la partie fractionnaire des jours si vous souhaitez un nombre de jours entier :

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;

Stocker les dates : choisissez un format et tenez-vous-y

Trois options raisonnables, classées selon la fréquence à laquelle vous devriez les choisir :

1. Texte ISO 8601 (TEXT). Lisible dans les dumps, tri correct, compatible avec toutes les fonctions de date SQLite. Le choix par défaut.
2. Secondes Unix (INTEGER). Compact, comparaisons rapides, aucune ambiguïté de fuseau horaire. Pratique quand vous avez des millions de lignes. Pour le relire, il faudra passer par datetime(col, 'unixepoch').
3. Jour julien (REAL). Rarement utile, sauf si vous faites beaucoup d'arithmétique sur les dates et que vous voulez la précision sous-seconde dans une seule colonne.

Ce qu'il ne faut surtout pas faire : mélanger les formats dans une même colonne. Les fonctions de date avaleront les deux sans broncher, mais les index, les tris et les comparaisons partiront en vrille.

-- Valeurs par défaut qui se remplissent automatiquement à l'insertion :
CREATE TABLE events (
    id          INTEGER PRIMARY KEY,
    name        TEXT NOT NULL,
    happened_at TEXT NOT NULL DEFAULT (datetime('now'))
);

INSERT INTO events (name) VALUES ('inscription'), ('connexion');

SELECT name, happened_at FROM events;

DEFAULT (datetime('now')) est l'équivalent SQLite de DEFAULT CURRENT_TIMESTAMP : chaque nouvelle ligne est automatiquement horodatée à l'heure UTC actuelle, sans la moindre ligne de code côté application.

Regrouper par période avec strftime

C'est précisément là que strftime prend tout son sens : pour regrouper les lignes par mois, par semaine ou par heure.

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 mois,
       COUNT(*)                     AS nombre_commandes
FROM orders
GROUP BY mois
ORDER BY mois;

Même principe pour « commandes par heure de la journée », « inscriptions par jour de la semaine » ou « événements par minute » : on choisit une chaîne de format qui ne capture que la granularité voulue, on regroupe dessus, puis on agrège.

La suite : les fonctions d'agrégation

Tiens, puisqu'on parle de regroupement — ce COUNT(*) n'est que la plus simple des fonctions d'agrégation de SQLite. On va voir dans la prochaine section l'ensemble complet : SUM, AVG, MIN, MAX, et comment elles condensent plusieurs lignes en une seule valeur récapitulative.