Here’s a scenario: Users are saving time only in a database as a string, e.g. 20:00. They expect something to happen every day at 8 PM. But 8 PM means a different time for different people given each lives in a different timezone, so the stored time is relative to each person.

Every hour, a server running in UTC runs a query to pull people for whom something should happen (we store their time zone separately).

The query boils down to comparing UTC server time (left side) with their relative stored time in the database (right side):

SELECT '20:00'::time = '12:00'::time AT TIME ZONE 'PST'

But there’s an issue on the right side: Although '12:00'::time is sort of a timezone-less object, '12:00'::time IN TIME ZONE 'PST' returns 04:00:00-08 as if we would be converting UTC time to PST time, which is not what we want.

Alternatively, I tried comparing these as strings using:

SELECT to_char('20:00'::time AT TIME ZONE 'PST', 'HH24:MI') = '12:00'

But this throws an error function to_char(time with time zone, unknown) does not exist. It seems that to_char cannot take time with zone?

So, trying to compare times or strings, I was not able to make either approach work. Any ideas on how to make either approach work? Any alternative approaches? Or any way to force postgres to treat 12:00 as 12 AM PST?