Skip to main content

// advent-of-postgres/day-14

Day 14 of 24IntermediateTime zonesAT TIME ZONE

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.

Opens 14 December 2026, 00:00 UTC

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.

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.