Five O’Clock  Somewhere

I came across an interesting problem yesterday. I have a PostgreSQL table full of users with a time_zone column stored as a string like “America/Detroit”. I needed to find all the users where it was the 5:00 hour.^[1]

Chris and I got playing with some queries and found how easy it is to grab the local time for a user:

# SELECT id, time_zone, now()::timestamp at time zone time_zone
#   FROM users
#   WHERE time_zone IS NOT NULL;
 
                  id                  |    time_zone    |           timezone            
--------------------------------------+-----------------+-------------------------------
 5d1c0abb-0c35-4454-9e19-6e50ccc6c37e | America/Detroit | 2023-03-17 15:48:44.17624
 dddbca1f-f30e-4997-80f8-c996c6008fac | America/Phoenix | 2023-03-17 12:48:44.17624
 
(2 rows)

You can do a bit more to get the current hour:

# select id, now()::timestamp with time zone at time zone time_zone,
#   date_part('hour', now()::timestamp with time zone at time zone time_zone) AS local_hour
#   FROM users
#   WHERE time_zone IS NOT NULL;
 
                  id                  |         timezone          | local_hour 
--------------------------------------+---------------------------+------------
 5d1c0abb-0c35-4454-9e19-6e50ccc6c37e | 2023-03-17 15:48:44.17624 |         15
 dddbca1f-f30e-4997-80f8-c996c6008fac | 2023-03-17 12:48:44.17624 |         12
 
(2 rows)

Now to select those users were it is 5:00 (I used 15 or 3:00 here, but you get the idea). Move the date_part to a WHERE:

# select id FROM users
#   WHERE date_part('hour', now()::timestamp with time zone at time zone time_zone) = 15;
 
                  id                  
--------------------------------------
 5d1c0abb-0c35-4454-9e19-6e50ccc6c37e
 
(1 row)

And that’s it! In a Rails app, I could use this like this:

# Who's in the 5:00 PM (17:00) hour?
User.where("date_part('hour', now()::timestamp with time zone at time zone time_zone) = ?", 17)

Enjoy!