Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE alarm(name text, t time, tz text);
INSERT INTO alarm VALUES
   ('Alfred', '04:00', 'Europe/Stockholm')  -- Alfred sets alarm for 4 AM.
 , ('Lotta',  '05:00', 'Europe/Stockholm')  -- Lotta sets alarm for 5 AM. 
 , ('Sharon', '11:00', 'Asia/Singapore');   -- Sharon sets alarm for 11 AM.
3 rows affected

-- winter
SELECT *
FROM   alarm
WHERE  (('2012-1-1'::date + t) AT TIME ZONE tz AT TIME ZONE 'UTC')::time = '03:00'::time;


name
t
tz


Alfred
04:00:00
Europe/Stockholm

Sharon
11:00:00
Asia/Singapore


-- summer
SELECT *
FROM   alarm
WHERE  (('2012-7-1'::date + t) AT TIME ZONE tz AT TIME ZONE 'UTC')::time = '03:00'::time;


name
t
tz


Lotta
05:00:00
Europe/Stockholm

Sharon
11:00:00
Asia/Singapore