// advent-of-postgres/day-14
The Late-Night Deploys
Office hours depend on where the office is. Count the deploys made outside working hours in each elf's own time zone.
The platform team works from six offices around the world. Bushy thinks too many deploys happen at night or on weekends, when nobody is around to fix them. The team agreed on one rule: deploy only on weekdays, between 09:00 and 17:00 in your own office's time zone.
The elves and their offices are in elves:
| Column | Type | Notes |
|---|---|---|
name |
text |
Primary key |
time_zone |
text |
An IANA time zone, for example Asia/Tokyo |
The deploys from 7 December 00:00 UTC to 14 December 00:00 UTC are in deploys. Count every row in the table:
| Column | Type | Notes |
|---|---|---|
id |
int |
Primary key |
author |
text |
References elves(name) |
deployed_at |
timestamptz |
When the deploy happened |
A deploy at exactly 17:00 local time is outside working hours. Saturday and Sunday are not weekdays. One of the offices is on summer time in December.
Your task
How many deploys were made outside working hours, in the author's own time zone?
Submit the number.
Stuck? Show a hint
deployed_at AT TIME ZONE e.time_zone turns a timestamptz into the local time of that office. Then check the weekday and the hour.
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