Fonctions de Date et Heure dans SQLite

SQLite offre un ensemble de fonctions puissantes pour travailler avec les dates et les heures. Contrairement à d’autres systèmes de gestion de bases de données, SQLite ne dispose pas d’un type de données spécifique pour les dates et les heures. Au lieu de cela, les valeurs temporelles peuvent être stockées sous trois formats différents :

1. Format texte ISO-8601 : par exemple '2023-05-17 14:30:00'
2. Nombre de jours juliens : le nombre de jours (avec fraction) depuis le 24 novembre 4714 av. J.-C. à midi
3. Timestamp Unix : le nombre de secondes écoulées depuis le 1er janvier 1970 à minuit UTC

NOTE

Le jour julien est un système de datation utilisé en astronomie, qui compte les jours de façon continue à partir d’une date fixe. C’est pratique pour calculer des intervalles de temps.

Dans ce tutoriel, nous allons explorer les principales fonctions de date et d’heure disponibles dans SQLite et voir comment les utiliser efficacement.

Les fonctions principales de date et heure

SQLite propose sept fonctions principales pour travailler avec les dates et les heures :

1. date() - Retourne la date au format YYYY-MM-DD
2. time() - Retourne l’heure au format HH:MM:SS
3. datetime() - Retourne la date et l’heure au format YYYY-MM-DD HH:MM:SS
4. julianday() - Retourne la date en nombre de jours juliens
5. unixepoch() - Retourne le timestamp Unix (secondes depuis 1970-01-01)
6. strftime() - Permet un formatage personnalisé des dates
7. timediff() - Calcule la différence entre deux dates au format lisible

Voyons comment utiliser ces fonctions avec des exemples concrets.

Obtenir la date et l’heure actuelles

-- Création d'une table pour stocker des événements
CREATE TABLE evenements (
  id INTEGER PRIMARY KEY,
  titre TEXT,
  date_creation TEXT,
  date_evenement TEXT
);

-- Insérer un événement avec la date et l'heure actuelles
INSERT INTO evenements (titre, date_creation, date_evenement)
VALUES ('Réunion d''équipe', datetime('now'), datetime('now', '+1 week'));

-- Récupérer la date et l'heure actuelles sous différents formats
SELECT
  date('now') AS date_aujourdhui,
  time('now') AS heure_actuelle,
  datetime('now') AS date_heure_complete,
  julianday('now') AS jour_julien,
  unixepoch('now') AS timestamp_unix;

Dans cet exemple :

• date('now') retourne la date actuelle au format YYYY-MM-DD
• time('now') retourne l’heure actuelle au format HH:MM:SS
• datetime('now') combine les deux au format YYYY-MM-DD HH:MM:SS
• julianday('now') et unixepoch('now') retournent des représentations numériques de la date actuelle

Manipuler les dates avec des modificateurs

L’une des fonctionnalités les plus puissantes des fonctions de date SQLite est la possibilité d’ajouter des modificateurs pour effectuer des calculs de date :

-- Démonstration des modificateurs de date
SELECT
  date('now') AS aujourdhui,
  date('now', '+1 day') AS demain,
  date('now', '+1 month') AS mois_prochain,
  date('now', '+1 year') AS annee_prochaine,
  date('now', '-7 days') AS semaine_derniere,
  date('now', 'start of month') AS debut_du_mois,
  date('now', 'start of month', '+1 month', '-1 day') AS fin_du_mois;

-- Trouver le premier mardi du mois courant
SELECT date('now', 'start of month', 'weekday 2');

-- Trouver la date du jour de l'an
SELECT date('now', 'start of year');

Les modificateurs peuvent être combinés pour effectuer des calculs de date complexes.

Extraire des parties d’une date avec strftime()

La fonction strftime() permet de formater des dates selon vos besoins et d’extraire des parties spécifiques :

-- Insertion de quelques événements pour démonstration
INSERT INTO evenements (titre, date_creation, date_evenement) VALUES
  ('Conférence annuelle', datetime('now'), '2023-11-15 09:00:00'),
  ('Formation SQL', datetime('now'), '2023-10-20 14:30:00'),
  ('Lancement de produit', datetime('now'), '2023-12-05 18:00:00');

-- Extraction de parties de date avec strftime()
SELECT
  titre,
  date_evenement,
  strftime('%Y', date_evenement) AS annee,
  strftime('%m', date_evenement) AS mois,
  strftime('%d', date_evenement) AS jour,
  strftime('%H', date_evenement) AS heure,
  strftime('%M', date_evenement) AS minute,
  strftime('%w', date_evenement) AS jour_semaine,
  strftime('%j', date_evenement) AS jour_annee
FROM evenements;

-- Formater une date de façon personnalisée
SELECT strftime('%d/%m/%Y à %Hh%M', date_evenement) AS date_francaise
FROM evenements;

Les spécificateurs de format les plus utiles pour strftime() sont :

• %Y : année à 4 chiffres
• %m : mois (01-12)
• %d : jour du mois (01-31)
• %H : heure (00-23)
• %M : minute (00-59)
• %S : seconde (00-59)
• %w : jour de la semaine (0-6, dimanche=0)
• %j : jour de l’année (001-366)

Calculer la différence entre dates

SQLite offre plusieurs façons de calculer la différence entre deux dates :

-- Calculer l'âge d'un événement en jours
SELECT
  titre,
  date_evenement,
  ROUND(julianday('now') - julianday(date_evenement)) AS jours_avant_evenement
FROM evenements;

-- Différence en secondes entre deux dates
SELECT
  titre,
  unixepoch(date_evenement) - unixepoch('now') AS secondes_restantes
FROM evenements;

-- Utilisation de timediff() pour une représentation humaine
SELECT
  titre,
  date_evenement,
  timediff(date_evenement, 'now') AS temps_restant
FROM evenements;

La fonction timediff() est particulièrement utile car elle retourne une représentation lisible de la différence entre deux dates au format : (+|-)YYYY-MM-DD HH:MM:SS.SSS

Gestion des fuseaux horaires

Par défaut, les fonctions de date SQLite utilisent l’heure UTC. Pour convertir en heure locale, utilisez le modificateur ‘localtime’ :

-- Comparaison entre UTC et heure locale
SELECT
  datetime('now') AS utc_maintenant,
  datetime('now', 'localtime') AS local_maintenant;

-- Conversion d'une date spécifique en heure locale
SELECT datetime('2023-11-20 12:00:00', 'localtime') AS heure_locale;

-- Conversion de l'heure locale vers UTC
SELECT datetime('now', 'utc') AS conversion_vers_utc;

Filtrer les données par date

Les fonctions de date sont très utiles pour filtrer les données dans des requêtes :

-- Filtrer les événements qui auront lieu ce mois-ci
SELECT titre, date_evenement
FROM evenements
WHERE strftime('%Y-%m', date_evenement) = strftime('%Y-%m', 'now');

-- Trouver les événements des 30 prochains jours
SELECT titre, date_evenement
FROM evenements
WHERE julianday(date_evenement) BETWEEN julianday('now') AND julianday('now', '+30 days');

-- Trouver les événements passés
SELECT titre, date_evenement
FROM evenements
WHERE date_evenement < datetime('now');

Précision au niveau des millisecondes

SQLite peut aussi gérer les millisecondes avec le modificateur ‘subsec’ :

-- Obtenir l'heure avec précision milliseconde
SELECT
  time('now', 'subsec') AS heure_precise,
  datetime('now', 'subsec') AS date_heure_precise,
  unixepoch('now', 'subsec') AS timestamp_precise;

Résumé des fonctions de date et heure

Fonction	Description	Exemple	Résultat
date(timevalue, modifier...)	Retourne la date au format YYYY-MM-DD	date('now')	2023-10-15
time(timevalue, modifier...)	Retourne l’heure au format HH:MM:SS	time('now')	14:30:45
datetime(timevalue, modifier...)	Retourne date et heure au format YYYY-MM-DD HH:MM:SS	datetime('now')	2023-10-15 14:30:45
julianday(timevalue, modifier...)	Retourne le jour julien (nombre de jours depuis -4713-11-24)	julianday('2023-10-15')	2460232.5
unixepoch(timevalue, modifier...)	Retourne le timestamp Unix en secondes	unixepoch('2023-10-15')	1697328000
strftime(format, timevalue, modifier...)	Formate la date selon le format spécifié	strftime('%d/%m/%Y', 'now')	15/10/2023
timediff(timevalue1, timevalue2)	Calcule la différence entre deux dates	timediff('2023-12-31', 'now')	+0000-02-15 00:00:00

Principaux modificateurs de date et heure

Modificateur	Description	Exemple
+N days	Ajoute N jours	date('now', '+7 days')
+N months	Ajoute N mois	date('now', '+3 months')
+N years	Ajoute N années	date('now', '+1 year')
start of month	Début du mois	date('now', 'start of month')
start of year	Premier jour de l’année	date('now', 'start of year')
start of day	Minuit le jour même	datetime('now', 'start of day')
weekday N	Prochain jour de semaine spécifié (0=dimanche)	date('now', 'weekday 1')
localtime	Convertit UTC en heure locale	datetime('now', 'localtime')
utc	Convertit l’heure locale en UTC	datetime('now', 'utc')
subsec	Ajoute la précision milliseconde	time('now', 'subsec')

Conclusion

Les fonctions de date et heure dans SQLite sont extrêmement utiles pour manipuler les données temporelles dans vos applications. Bien qu’elles ne soient pas aussi nombreuses que dans d’autres SGBD, elles offrent une flexibilité suffisante pour la plupart des cas d’utilisation courants.

En maîtrisant ces fonctions, vous pourrez :

• Stocker et récupérer des dates dans différents formats
• Effectuer des calculs complexes sur les dates
• Filtrer efficacement vos données par période
• Formater les dates selon les besoins spécifiques de votre application

N’oubliez pas que SQLite traite les dates comme des chaînes de texte ou des nombres, ce qui peut parfois nécessiter une attention particulière pour garantir la cohérence de vos données temporelles.