lesson 34 Dates, strings & subqueries · intermediate

Date boundaries & monthly buckets

Define date ranges and monthly groups without boundary mistakes.

the idea

For timestamps, use a half-open interval: >= start and < next boundary. This includes every time on the last day without guessing the final second. SQLite uses strftime/date; PostgreSQL uses DATE_TRUNC and INTERVAL. All teaching timestamps are UTC.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

SELECT id,watched_at
FROM watch_events
WHERE watched_at >= '2025-01-01' AND watched_at < '2025-02-01';

Watch for: BETWEEN with an end date at midnight can omit that date’s later events.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1. 1

   Bound January with half-open dates. Timestamps need a clear start and end. Use >= month start and < next month start so the boundary stays unambiguous.

   step-1.sql Run

   sql›

   SELECT id, watched_at
   FROM watch_events
   WHERE watched_at >= '2025-01-01' AND watched_at < '2025-02-01'
   ORDER BY watched_at
   LIMIT 8;

2. 2

   Select the January window. The half-open range includes midnight on January 1 and excludes midnight on February 1.

   step-2.sql Run

   sql›

   SELECT id, watched_at
   FROM watch_events
   WHERE watched_at >= '2025-01-01' AND watched_at < '2025-02-01';