Your Query Exercise was to find the best time to take the database offline for maintenance – the time when we’ve got the least load. We kept this one pretty simple by looking at the data in just one table, and we didn’t hassle with time zones. We just wanted the 3 lowest-load hours, in this format:
- Mondays, 1:00-1:59
- Saturdays, 23:00-23:59
- Sundays, 2:00-2:59
Kinda-Safe Technique:
Grouping by date_part
One way to do this is to use the built-in date_part function to group users by weekday and hour of their creation dates, like this:
|
1 2 3 4 5 6 7 8 |
select date_part('dow', creationdate) as weekday, date_part('hour', creationdate) as hour, COUNT(*) as users_created from users u where u.creationdate >= '2023-11-05' and u.creationdate < '2023-12-03' group by date_part('dow', creationdate), date_part('hour', creationdate) order by COUNT(*); |
And at first glance, that looks like it works:
It looks like late Sunday night, early Monday mornings are the best times:
- Monday 00:00-00:59
- Sunday 23:00-23:59
- Monday 01:00-01:59
But…there’s a catch. This technique only works as long as our historical data is populated for every hour of every day. What if we’ve been taking the web servers down every Sunday at 16:00-17:00? There wouldn’t be any users created in that date range, and thus, no rows would show up in the result set for that query!
The Safer Technique:
generate a list of days/times first.
In pseudocode:
|
1 2 3 |
SELECT weekday, hour, (SELECT COUNT(*) of users_created in that weekday and hour) FROM list_of_weekdays_and_hours |
That way, if we have any weekday/hour combinations with no users created, we’ll still have rows for them in our result set. In Postgres, one option for this is the generate_series function, which we discussed in a recent Technique of the Week.
The generate_series function does take timestamp parameters, but that’s probably overkill for what we’re doing here. We just need:
- weekdays: a set of rows 0 through 6
- hours: a set of rows 0 through 23
So we’ll call generate_series twice:
|
1 2 3 4 |
select weekdays.weekday, hours.hour from (select generate_series(0, 6, 1) as weekday) as weekdays cross join (select generate_series(0, 23, 1) as hour) as hours order by weekdays.weekday, hours.hour; |
Like the bus driver said, you see where I’m going with this:
It just so happens that there are 168 rows (7 days x 24 hours = 168), and our original grouping-by-datepart result set had 168 rows, so in this case, we already know we’re safe. However, let’s finish the exercise out because sooner or later, you’re gonna hit a result set where you need this technique.
I’ll use the query we wrote earlier as a CTE, and join to it:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
with users_aggregate as ( select date_part('dow', creationdate) as weekday, date_part('hour', creationdate) as hour, COUNT(*) as users_created from users u where u.creationdate >= '2023-11-05' and u.creationdate < '2023-12-03' group by date_part('dow', creationdate), date_part('hour', creationdate) ) select weekdays.weekday, hours.hour, coalesce(ua.users_created, 0) as users_created from (select generate_series(0, 6, 1) as weekday) as weekdays cross join (select generate_series(0, 23, 1) as hour) as hours left outer join users_aggregate ua on weekdays.weekday = ua.weekday and hours.hour = ua.hour order by coalesce(ua.users_created, 0); |
And there you have it! Hope you enjoyed the exercise. There are certainly lots of ways to accomplish this – subqueries, generate_series with timestamps, and more.