SQL Date and Time — Basics of Month Ends, Zones, Elapsed Time

SELECT
  DATE_TRUNC('day', occurred_at AT TIME ZONE 'Asia/Tokyo')::date AS jst_day,  -- convert to JST first, then truncate to the day
  COUNT(*) AS events
FROM     events_utc
GROUP BY DATE_TRUNC('day', occurred_at AT TIME ZONE 'Asia/Tokyo')
ORDER BY jst_day;

/*
  Execution order (logical evaluation order):
  1. FROM events_utc                       → Read 5 rows
  2. GROUP BY DATE_TRUNC('day', JST value) → Split into 3 groups
  3. SELECT COUNT(*)                       → Count per group
  4. ORDER BY jst_day                      → Ascending by date
  */

SELECT
  a.room_id,
  a.booking_id AS booking_a,
  b.booking_id AS booking_b
FROM   bookings a
JOIN   bookings b
  ON   a.room_id = b.room_id           -- compare within the same room only
  AND  a.booking_id < b.booking_id  -- drop self-pairs and mirrored duplicates
  AND  a.starts_at < b.ends_at     -- overlap condition (first half)
  AND  b.starts_at < a.ends_at     -- overlap condition (second half)
ORDER BY booking_a, booking_b;

/*
  Execution order (logical evaluation order):
  1. FROM bookings a                → Read 5 rows
  2. JOIN bookings b (room+overlap) → Narrow to 1 pair
  3. SELECT room_id, booking_a, …   → Select 3 columns
  4. ORDER BY booking_a, booking_b  → Ascending by id
  */

SELECT
  category,
  COUNT(*) AS closed_tickets,
  ROUND(AVG(EXTRACT(EPOCH FROM (closed_at - created_at)) / 3600)::numeric, 2) AS avg_hours  -- seconds → hours → average → rounded
FROM     tickets
WHERE    closed_at IS NOT NULL         -- keep unfinished tickets out of the population
GROUP BY category
ORDER BY category;

/*
  Execution order (logical evaluation order):
  1. FROM tickets                   → Read 5 rows
  2. WHERE closed_at IS NOT NULL    → Narrow to 4 rows
  3. GROUP BY category              → Split into 2 groups
  4. SELECT COUNT / AVG(EPOCH…)     → Count and average hours
  5. ORDER BY category              → Ascending by category
  */

SELECT
  member_id,
  birth_on,
  EXTRACT(YEAR FROM AGE(DATE '2026-09-01', birth_on)) AS age  -- take only the year part of the difference
FROM   members
ORDER BY age DESC;

/*
  Execution order (logical evaluation order):
  1. FROM members                 → Read 4 rows
  2. SELECT year part of AGE      → Output 3 columns
  3. ORDER BY age DESC            → Descending by age
  */