Working with Dates and Times

Date arithmetic, OVERLAPS, and querying daterange columns

Festivals run for a week. Beers have seasonal availability windows. Streaming rights have validity periods. All of these are intervals of time, not single moments.\n\nPostgreSQL has a native daterange type that stores an interval as a single value and supports range operators: @> (does range contain this date?), && (do two ranges overlap?), and <@ (is date within range?). These operators use GiST indexes — they are fast and expressive.\n\nbeer_db has seasonal_availability (daterange) on beers and held_during (daterange) on festivals. tv_db has valid_from / valid_until date columns on streaming_rights.

Basic Date Filtering

-- Filter by exact date or range
SELECT name, premiere_date
FROM seasons
WHERE premiere_date >= '2022-01-01'
  AND premiere_date < '2023-01-01';

-- BETWEEN is inclusive on both ends
WHERE premiere_date BETWEEN '2022-01-01' AND '2022-12-31';

-- Relative to today
WHERE premiere_date > CURRENT_DATE - INTERVAL '90 days';

Extracting Date Parts

SELECT name,
       EXTRACT(year  FROM premiere_date) AS premiere_year,
       EXTRACT(month FROM premiere_date) AS premiere_month
FROM festivals;

Querying daterange Columns

The festivals.held_during and beers.seasonal_availability columns are native daterange values. Ranges use interval notation: [ means inclusive (closed), ) means exclusive (open).

-- Which festivals were held in 2023?
SELECT name, held_during
FROM festivals
WHERE held_during && daterange('2023-01-01', '2024-01-01');

-- Which beers are in season right now?
SELECT name, seasonal_availability
FROM beers
WHERE seasonal_availability @> CURRENT_DATE;

-- Access the start and end dates of a range
SELECT name,
       lower(held_during) AS starts,
       upper(held_during) AS ends
FROM festivals;

Streaming Rights Date Columns (tv_db)

The streaming_rights table uses plain date columns (valid_from, valid_until) rather than a range type. Query them with standard comparisons:

SELECT show_id, region, valid_from, valid_until
FROM streaming_rights
WHERE valid_from <= CURRENT_DATE
  AND (valid_until IS NULL OR valid_until >= CURRENT_DATE);

daterange type

A PostgreSQL range type that stores a start and end date as a single value. Ranges can be open (unbounded) or closed at either end. The bracket notation [ means inclusive (the endpoint is part of the range), ) means exclusive (the endpoint is not included). Range types support operators like @> (contains), && (overlaps), and @< (is left of).

INTERVAL arithmetic

PostgreSQL allows adding and subtracting intervals from dates and timestamps. INTERVAL is expressed in human-readable units: '90 days', '3 months', '1 year'. This enables relative date queries without computing exact dates in application code.

🔌 Connect to beer_db

Connect to beer_db as the beer_db user to query festivals and beers with date ranges.

psql -U beer_db -d beer_db

psql (18.4) Type "help" for help. beer_db=>

🗓️ List Festivals with Start and End Dates

Query the festivals table and extract the start and end dates from the held_during daterange column. Use lower() and upper() to decompose the range into readable date columns.

SELECT name, city, lower(held_during) AS starts, upper(held_during) AS ends FROM festivals ORDER BY starts;

📅 Find Festivals Held in 2023

Use the && overlap operator to find all festivals whose held_during range overlaps with the year 2023. A festival that spans the year boundary would also be found.

SELECT name, city, held_during FROM festivals WHERE held_during && daterange('2023-01-01', '2024-01-01') ORDER BY lower(held_during);

🍺 Find Beers Available in a Specific Season

Use the @> contains operator to find beers whose seasonal_availability range contains a specific date. Use DATE '2024-06-15' as the target — the ranges in this dataset are from 2024. Include beers with NULL seasonal_availability too — those are available year-round. Order by seasonal_availability NULLS LAST so the seasonal beers with actual range values appear first.

SELECT name, seasonal_availability FROM beers WHERE seasonal_availability @> DATE '2024-06-15' OR seasonal_availability IS NULL ORDER BY seasonal_availability NULLS LAST;

🌐 Switch to tv_db: Check Active Streaming Rights

Connect to tv_db and find streaming rights that are currently active (valid_from ≤ today ≤ valid_until). This database uses plain date columns rather than a daterange type. Join with the shows table to get show titles and order by title.

\c tv_db tv_db

SELECT s.title, sr.region, sr.valid_from, sr.valid_until FROM streaming_rights sr JOIN shows s ON s.id = sr.show_id WHERE sr.valid_from <= '2025-06-15' AND sr.valid_until >= '2025-06-15' ORDER BY title LIMIT 15;

You are now connected to database "tv_db" as user "tv_db". title | region | valid_from | valid_until ----------------+--------+------------+------------- Afterburn | DE | 2020-12-26 | 2025-12-31 Afterburn | AU | 2020-09-27 | 2025-12-31 Afterburn | US | 2020-01-01 | 2025-12-31 Afterburn | CA | 2020-06-29 | 2025-12-31 Afterburn | GB | 2020-03-31 | 2025-12-31 Apex Predator | AU | 2020-09-27 | 2025-12-31 Apex Predator | DE | 2020-12-26 | 2025-12-31 Apex Predator | CA | 2020-06-29 | 2025-12-31 Apex Predator | US | 2020-01-01 | 2025-12-31 Apex Predator | GB | 2020-03-31 | 2025-12-31 Arcane Witness | GB | 2020-03-31 | 2025-12-31 Arcane Witness | US | 2020-01-01 | 2025-12-31 Arcane Witness | CA | 2020-06-29 | 2025-12-31 Arcane Witness | AU | 2020-09-27 | 2025-12-31 Arcane Witness | DE | 2020-12-26 | 2025-12-31 (15 rows)

Lab complete! You can query dates and date ranges precisely:\n\n\n CURRENT_DATE : ✅ today's date at query time\n date + INTERVAL 'n days' : ✅ relative date arithmetic\n EXTRACT(year FROM col) : ✅ pull out date parts\n range @> date : ✅ containment check\n range && range : ✅ overlap check\n

Enable JavaScript to run the live terminal and track your progress.