Funzioni su date e timestamp

Date e timestamp sono ovunque: ordini, login, eventi, fatturazione. In questa lezione vediamo come estrarre parti da una data (anno, mese…), troncarla a una granularità (mese, settimana…), calcolare intervalli e differenze, e fare confronti su periodi.

I tipi base

PostgreSQL distingue:

• DATE — solo data, formato YYYY-MM-DD;
• TIMESTAMP — data + ora, senza fuso orario;
• TIMESTAMPTZ — data + ora + fuso orario;
• INTERVAL — un periodo (es. '3 days', '2 hours 30 minutes').

Nel nostro dataset ecommerce:

EXTRACT — leggere una parte

EXTRACT(parte FROM data) estrae un numero:

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;

Le parti più usate: YEAR, MONTH, DAY, HOUR, MINUTE, DOW (giorno settimana 0-6), DOY (giorno dell'anno).

DATE_TRUNC — arrotondare per difetto

DATE_TRUNC('parte', data) "tronca" la data a una granularità. È fondamentale per aggregare per mese, settimana, ora:

-- 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;

Parti valide: 'year', 'quarter', 'month', 'week', 'day', 'hour', 'minute', 'second'.

Per leggibilità si fa spesso il cast a DATE quando non serve l'ora: DATE_TRUNC('month', ordered_at)::date.

Differenze e intervalli

Sottrarre due TIMESTAMP produce un INTERVAL:

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

Confronti con un letterale INTERVAL:

WHERE  shipped_at - ordered_at > INTERVAL '1 day'

Aggiungere giorni a una data:

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

Prova tu

Esercizio di ripasso