Dates, Time Series, Gaps and Islands

Description

Almost every product dashboard is about time: signups per day, daily active users, revenue this month, a user's login streak. The data is a list of moments - "logged in at 22:30 UTC" - and the questions are about calendar days, weeks and runs of days. Turning one into the other is where this lesson lives.

It's an area full of quiet bugs. Four that survive a quick look at a typical dashboard:

• The signups chart skips March 5 - nobody signed up, so there's no row, and the line jumps straight from the 4th to the 6th.
• "Logins from March 2 through March 6" leaves out almost everything that happened on March 6.
• A customer in New York who logs in at 10:30 PM on Monday is counted on Tuesday.
• The streak badge tells someone who logged in three days running that their streak is two - because they logged in twice on one of those days.

By the end you'll fix each of them. Step by step:

1. Grouping moments into days, weeks and 15-minute buckets.
2. Date ranges that don't lose the last day.
3. Time zones - whose "Monday" is it?
4. Showing empty days with a generated calendar.
5. Streaks (gaps and islands) and sessions.
6. Cohorts and retention.

Examples use nine logins (user_id, logged_in_at as timestamptz) and a few page_views; the session's time zone is UTC. Every result was produced by PostgreSQL 18.

Buckets: date_trunc

date_trunc('day' | 'week' | 'month' | ..., ts) rounds a timestamp down to the start of its bucket. Group by it and aggregate:

SELECT date_trunc('day', logged_in_at)::date AS day,
       COUNT(*)                AS logins,
       COUNT(DISTINCT user_id) AS users
FROM logins
GROUP BY 1
ORDER BY 1;

date_trunc returns a timestamp; ::date makes it a clean day. "Active users" is COUNT(DISTINCT user_id) - user 1 logged in twice on March 2 but is one active user.

Weeks are ISO weeks and start on Monday - a surprise if your calendar starts on Sunday. For Sunday weeks, shift by a day and back:

SELECT ts,
       date_trunc('week', ts) AS iso_week,
       (date_trunc('week', ts + interval '1 day') - interval '1 day')::date AS sunday_week
FROM (VALUES (timestamptz '2026-03-07 15:00+00'),
             (timestamptz '2026-03-08 15:00+00')) AS t(ts)
ORDER BY ts;

For buckets that aren't a calendar unit - 5 or 15 minutes - date_bin(interval '15 minutes', ts, timestamptz '2026-01-01') (Postgres 14+) cuts time into fixed strides counted from an origin: 09:37:12 lands in the 09:30 bucket.

Which type for which value?

Date ranges: half-open, not BETWEEN

On a timestamp, BETWEEN '2026-03-02' AND '2026-03-06' means up to March 6 at 00:00:00 - the rest of March 6 is outside. Ending at '2026-03-07' instead counts a login at exactly midnight on the 7th:

SELECT
    COUNT(*) FILTER (WHERE logged_in_at BETWEEN '2026-03-02' AND '2026-03-06') AS between_6th,
    COUNT(*) FILTER (WHERE logged_in_at BETWEEN '2026-03-02' AND '2026-03-07') AS between_7th,
    COUNT(*) FILTER (WHERE logged_in_at >= '2026-03-02'
                       AND logged_in_at <  '2026-03-07')                     AS half_open
FROM logins;

The half-open range >= start AND < next_start is right for any unit - March is >= '2026-03-01' AND < '2026-04-01' - with no "last second of the day" to guess, and an index on the column can serve it. WHERE logged_in_at::date = '2026-03-06' is correct too, but wrapping the column in a cast hides it from a plain index.

Time zones

A timestamptz stores an instant, not a time zone. Everything that turns it into calendar values - ::date, date_trunc, display - uses the session's TimeZone setting, UTC here. For a New York day, convert first: ts AT TIME ZONE 'America/New_York' is the wall-clock time in New York, as a timestamp without time zone.

SELECT id, logged_in_at,
       logged_in_at::date                               AS utc_day,
       (logged_in_at AT TIME ZONE 'America/New_York')::date AS new_york_day
FROM logins
WHERE id IN (2, 5, 9)
ORDER BY id;

Login 5 was at 10:30 PM on March 2 in New York - a March 2 login for that customer, a March 3 login in UTC.

Use named zones, never fixed offsets. New York is UTC-5 in winter and UTC-4 from March 8 to November 1, 2026; 'EST' and - interval '5 hours' are UTC-5 all year. The US and Europe also switch on different dates - London moves to summer time on March 29 - so for three weeks New York is four hours behind London, not five:

SELECT ts,
       ts AT TIME ZONE 'America/New_York' AS new_york,
       ts AT TIME ZONE 'EST'              AS fixed_est,
       ts AT TIME ZONE 'Europe/London'    AS london
FROM (VALUES (timestamptz '2026-03-07 12:00+00'),
             (timestamptz '2026-03-09 12:00+00'),
             (timestamptz '2026-03-30 12:00+00')) AS t(ts)
ORDER BY ts;

Two shortcuts: date_trunc('day', ts, 'America/New_York') truncates in that zone (and returns the instant local midnight happened), and SET TIME ZONE 'America/New_York' makes every cast in the session follow it. Store timestamptz, convert at the edge, and put the zone in the report's definition.

Filling gaps with generate_series

GROUP BY can't produce a row for a day with no data. Generate the calendar, then LEFT JOIN the data to it:

SELECT d::date      AS day,
       COUNT(*)    AS count_star,
       COUNT(l.id) AS logins
FROM generate_series(timestamptz '2026-03-02', timestamptz '2026-03-08', interval '1 day') AS d
LEFT JOIN logins l ON l.logged_in_at >= d
                  AND l.logged_in_at <  d + interval '1 day'
GROUP BY d
ORDER BY d;

March 5 and 8 had no logins, and they're there. Count a column from the joined table: COUNT(*) counts the calendar row itself, so an empty day shows 1. For sums, COALESCE(SUM(x), 0). The join uses a half-open range, so the midnight login (9) lands on March 7 only.

A local day isn't always 24 hours. Stepping interval '1 day' from a New York midnight in a UTC session drifts by an hour at the daylight-saving switch; since Postgres 16, generate_series takes a time zone and steps in that zone's days:

SELECT d AS utc_instant, d AT TIME ZONE 'America/New_York' AS new_york
FROM generate_series(timestamptz '2026-03-07 05:00+00', timestamptz '2026-03-09 04:00+00',
                     interval '1 day', 'America/New_York') AS d
ORDER BY d;

Gaps and islands: streaks

A run of consecutive days is an island; the missing days between runs are gaps. The classic trick: take one row per user and day, number the days in order, and subtract the number from the date. Along a run both go up by one, so the difference stays the same:

WITH days AS (
    SELECT DISTINCT user_id, logged_in_at::date AS day FROM logins
)
SELECT day,
       ROW_NUMBER() OVER (ORDER BY day)            AS rn,
       day - ROW_NUMBER() OVER (ORDER BY day)::int AS island
FROM days
WHERE user_id = 1
ORDER BY day;

The key is just a label - it isn't a meaningful date. Group by it to get each run:

WITH days AS (
    SELECT DISTINCT user_id, logged_in_at::date AS day FROM logins
), islands AS (
    SELECT user_id, day,
           day - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY day)::int AS island
    FROM days
)
SELECT user_id, MIN(day) AS first_day, MAX(day) AS last_day, COUNT(*) AS days
FROM islands
GROUP BY user_id, island
ORDER BY user_id, first_day;

The DISTINCT matters: user 1 logged in twice on March 2. With two rows for one day the row number rises twice while the date doesn't, and the island splits in two. Subtract from the date itself - not the day of the month - so a run can cross into a new month. (ROW_NUMBER is a bigint, and date - integer needs the ::int.)

Sessions: islands in time

A session is an island whose gap is time: a new session starts when a user has been idle for more than 30 minutes. Flag the first row of each session with LAG, then a running SUM of the flags numbers them:

WITH flagged AS (
    SELECT user_id, viewed_at,
           CASE WHEN viewed_at - LAG(viewed_at) OVER (PARTITION BY user_id ORDER BY viewed_at)
                     <= interval '30 minutes'
                THEN 0 ELSE 1 END AS starts_session
    FROM page_views
), numbered AS (
    SELECT user_id, viewed_at,
           SUM(starts_session) OVER (PARTITION BY user_id ORDER BY viewed_at) AS session
    FROM flagged
)
SELECT user_id, session, MIN(viewed_at) AS started, MAX(viewed_at) AS ended, COUNT(*) AS views
FROM numbered
GROUP BY user_id, session
ORDER BY user_id, session;

A user's first view has no previous one, so the comparison is NULL and the CASE falls through to ELSE 1 - it opens session 1. The same flag-and-sum pattern finds any kind of run: a price's unchanged periods, a device's offline spells, consecutive failed payments.

Cohorts and retention

A cohort groups users by when they started; retention is the share of a cohort that came back in a later window. Keep everyone in the cohort with a LEFT JOIN, and count users, not logins:

WITH days AS (
    SELECT DISTINCT user_id, logged_in_at::date AS day FROM logins
), firsts AS (
    SELECT user_id, MIN(day) AS first_day FROM days GROUP BY user_id
)
SELECT f.first_day AS cohort,
       COUNT(*)         AS users,
       COUNT(d.user_id) AS back_next_day,
       ROUND(100.0 * COUNT(d.user_id) / COUNT(*), 1) AS retention_pct
FROM firsts f
LEFT JOIN days d ON d.user_id = f.user_id AND d.day = f.first_day + 1
GROUP BY f.first_day
ORDER BY f.first_day;

An inner join would drop user 2, who didn't come back, and report the March 2 cohort at 100%. Define windows in calendar days (first_day + 1 through first_day + 7) rather than 24-hour periods from the signup moment, and leave out cohorts too recent to have a full window yet.

Where it breaks

• BETWEEN on timestamps - stops at midnight of the last day; use >= start AND < next_start.
• UTC days for local users - ::date and date_trunc use the session's time zone; convert with AT TIME ZONE 'America/New_York'.
• Fixed offsets ('EST', - interval '5 hours') - wrong for more than half the year; name the zone.
• Missing periods - a chart without empty days, or a LAG that skips one; generate the calendar and count a joined column, not *.
• Duplicates in gaps and islands - reduce to one row per user and day before numbering.
• Retention without the non-returners - an inner join makes every cohort 100%.

Tradeoffs

• A generated calendar vs a calendar table: generate_series needs nothing stored; a permanent calendar table can carry business facts - holidays, fiscal weeks - that reports join to.
• Row-number islands vs LAG flags: date - ROW_NUMBER() is short when the step is exactly one day; LAG plus a running SUM handles any rule ("idle for more than 30 minutes", "status changed").
• Bucketing in SQL vs in the app: local days have to exist before GROUP BY runs, so the conversion belongs in the query - with the zone as a parameter, not hard-coded.

Going further. The deep dive (next tab) covers what the time types store, why interval '1 month' and '30 days' compare equal but add differently, which indexes serve time ranges (and the cast that defeats them), BRIN indexes and partitioning for big time-series tables, what now() really returns, and how to store future events safely.

Check yourself

1. WHERE created_at BETWEEN '2026-03-01' AND '2026-03-31' on a timestamptz column. Which March orders are missing?

2. A chart of signups per day built with GROUP BY signed_up_at::date has no row for a quiet Sunday. How do you get a 0 there?

3. A user logged in on March 2, twice on March 3, and on March 4. Why does day - ROW_NUMBER() over their logins find two streaks instead of one, and what's the fix?

Key takeaways

• Bucket with date_trunc (ISO weeks start on Monday) or date_bin; filter with half-open ranges, not BETWEEN.
• A timestamptz is an instant, and its date depends on the session's time zone - convert with a named zone, never a fixed offset.
• Empty periods need a generated calendar and a LEFT JOIN; count a joined column.
• Islands: one row per unit, then date - ROW_NUMBER(). Sessions: flag the gap with LAG, number with a running SUM.
• Retention: cohort by start date, LEFT JOIN the later activity, count users, and define windows in calendar days.