Skip to main content

// advent-of-postgres/day-13

Day 13 of 24Intermediategenerate_seriesGaps in time series

The Missing Hours

A reading with no value and no reading at all are different problems. Find the hours when the reindeer collars went silent.

Opens 13 December 2026, 00:00 UTC

The reindeer wear collars that report their heart rate every five minutes. Shinny suspects the uplink from the stables drops out for hours at a time, which would explain the gaps on the training charts.

The readings are in telemetry:

Column Type Notes
service text Always reindeer-telemetry
reported_at timestamptz When the reading arrived
heart_rate int null when a sensor sent an empty reading

An empty reading means a collar was online but its sensor failed. When the uplink is down, nothing arrives at all.

Your task

How many hours of 13 December (UTC, counting from 00:00 to 24:00) had no readings at all?

Submit the number of hours.

Stuck? Show a hint

GROUP BY can only show hours that have rows. Generate all 24 hours with generate_series and look for the ones with nothing.

No database yet. You get a real Postgres database on Neon, free, with no account.
One statement at a time. Ctrl+Enter or Cmd+Enter runs it.

Download today's data as SQL to solve it on your own Postgres (14 or later).

Solved this puzzle? Share it.

Tag @thedevopsdaily and share it with the community.