Datum- en tijdstempelfuncties

Datums en tijdstempels zijn overal: bestellingen, logins, evenementen, facturering. In deze les zien we hoe je delen uit een datum (jaar, maand…) kunt extraheren, kort het af tot een granulariteit (maand, week...), bereken intervallen en verschillen, en vergelijkingen tussen perioden maken.

De basistypen

PostgreSQL onderscheidt:

• DATE — alleen datum, formaat YYYY-MM-DD;
• TIMESTAMP — datum + tijd, geen tijdzone;
• TIMESTAMPTZ — datum + tijd + tijdzone;
• INTERVAL — een punt (bijvoorbeeld '3 days', '2 hours 30 minutes').

In onze ecommerce dataset:

EXTRACT — een gedeelte lezen

EXTRACT(part FROM date) extraheert een getal:

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;

De meest gebruikte onderdelen: YEAR, MONTH, DAY, HOUR, MINUTE, DOW (dag van week 0-6), DOY (dag van het jaar).

DATE_TRUNC — naar beneden afgerond

DATE_TRUNC('part', date) "kapt" de datum af tot een granulariteit. Het is essentieel voor aggregatie per maand, week, uur:

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

Geldige onderdelen: 'year', 'quarter', 'month', 'week', 'day', 'hour', 'minute', 'second'.

Voor de leesbaarheid casten mensen vaak naar DATE als er geen tijd nodig is: DATE_TRUNC('month', ordered_at)::date.

Verschillen en intervallen

Het aftrekken van twee TIMESTAMPs levert een INTERVAL op:

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

Vergelijkingen met een letterlijke INTERVAL:

WHERE  shipped_at - ordered_at > INTERVAL '1 day'

Dagen aan een datum toevoegen:

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

Probeer het

Oefening beoordelen