// advent-of-postgres/day-13
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.
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.
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.
We earn commissions when you shop through the links below.
Bitrise
Managed runners for GitHub Actions
Bitrise Build Hub runs your GitHub Actions jobs on M4 Macs and AMD Linux machines. Change runs-on and keep your workflows, secrets and logs on GitHub.
Atomsized
AWS platform engineering and GitOps
Design and automation for reliable AWS and Kubernetes platforms, safer delivery workflows, and preview and UAT environments your engineers can understand and own.
DigitalOcean
Cloud infrastructure for developers
Simple, reliable cloud computing designed for developers
DevDojo
Developer community & tools
Join a community of developers sharing knowledge and tools
SMTPfast
Developer-first email API
Send transactional and marketing email through a clean REST API. Detailed logs, webhooks, and embeddable signup forms in one dashboard.
QuizAPI
Developer-first quiz platform
Build, generate, and embed quizzes with a powerful REST API. AI-powered question generation and live multiplayer.
Want to support DevOps Daily and reach thousands of developers?
Become a Sponsor