日付とタイムスタンプの関数

Dates and timestamps are everywhere: orders, logins, events, billing. In this lesson we see how to extract parts from a date (year, month…), truncate it to a granularity (month, week…), compute intervals and differences, and make comparisons across periods.

The base types

PostgreSQL distinguishes:

• DATE — date only, format YYYY-MM-DD;
• TIMESTAMP — date + time, no timezone;
• TIMESTAMPTZ — date + time + timezone;
• INTERVAL — a period (e.g. '3 days', '2 hours 30 minutes').

In our ecommerce dataset:

EXTRACT — reading a part

EXTRACT(part FROM date) extracts a number:

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;

The most used parts: YEAR, MONTH, DAY, HOUR, MINUTE, DOW (day of week 0-6), DOY (day of the year).

DATE_TRUNC — rounding down

DATE_TRUNC('part', date) "truncates" the date to a granularity. It is essential for aggregating by month, week, hour:

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

Valid parts: 'year', 'quarter', 'month', 'week', 'day', 'hour', 'minute', 'second'.

For readability, people often cast to DATE when time is not needed: DATE_TRUNC('month', ordered_at)::date.

Differences and intervals

Subtracting two TIMESTAMPs produces an INTERVAL:

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

Comparisons with an INTERVAL literal:

WHERE  shipped_at - ordered_at > INTERVAL '1 day'

Adding days to a date:

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

Try it

Review exercise