Fonctions de date et d'horodatage

Les dates et les horodatages sont partout : commandes, connexions, événements, facturation. Dans cette leçon nous voyons comment extraire des parties d'une date (année, mois…), tronquez-le à une granularité (mois, semaine…), calculez des intervalles et les différences, et faire des comparaisons entre les périodes.

Les types de bases

PostgreSQL distingue :

• DATE — date uniquement, format YYYY-MM-DD ;
• TIMESTAMP — date + heure, pas de fuseau horaire ;
• TIMESTAMPTZ — date + heure + fuseau horaire ;
• INTERVAL — un point (par exemple '3 days', '2 hours 30 minutes').

Dans notre ensemble de données ecommerce :

EXTRACT — lecture d'une partie

EXTRACT(part FROM date) extrait un nombre :

SELECT EXTRACT(YEAR  FROM ordered_at) AS year,
       EXTRACT(MONTH FROM ordered_at) AS month,
       EXTRACT(DOW   FROM ordered_at) AS day_of_week  -- 0=domenica
FROM   orders;

Les pièces les plus utilisées : YEAR, MONTH, DAY, HOUR, MINUTE, DOW (jour de la semaine 0-6), DOY (jour de l'année).

DATE_TRUNC — arrondi à l'inférieur

DATE_TRUNC('part', date) "tronque" la date à une granularité. C'est indispensable pour agréger par mois, semaine, heure :

-- Tutti gli ordini fatti in "marzo 2025" cadono nello stesso bucket:
SELECT DATE_TRUNC('month', ordered_at) AS month,
       COUNT(*) AS order_count
FROM   orders
GROUP BY month
ORDER BY month;

Pièces valides : 'year', 'quarter', 'month', 'week', 'day', 'hour', 'minute', 'second'.

Pour des raisons de lisibilité, les gens lancent souvent un cast vers DATE lorsque le temps n'est pas nécessaire : DATE_TRUNC('month', ordered_at)::date.

Différences et intervalles

La soustraction de deux TIMESTAMP produit un INTERVAL :

SELECT id,
       shipped_at - ordered_at AS wait_time
FROM   orders
WHERE  shipped_at IS NOT NULL;

Comparaisons avec un littéral INTERVAL :

WHERE  shipped_at - ordered_at > INTERVAL '1 day'

Ajouter des jours à une date :

SELECT signed_up_on + INTERVAL '30 days' AS trial_expiration
FROM   customers;

Essayez-le

Exercice de révision