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¹.

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!

¹Okay, I really wanted a different time, but I was working on this at 4:00 on a Friday.