postgresqltimezonetimestamp

Return rows based on timestamp respective to their time zone

I have a table in which every row represents a user. I am also storing the user's time zone as text such as 'America/Denver', 'America/New_York' etc.

Is it possible to write a query that would return users for whom their current time of day is between 1 PM to 11 PM respective to their time zone?

Solution

  • Given this table:

    CREATE TABLE usr (
   usr_id serial PRIMARY KEY
,  usr    text NOT NULL
,  tz     text  -- time zone names
);

    Use the AT TIME ZONE construct:

    SELECT *, (now() AT TIME ZONE tz)::time AS local_time
FROM   usr
WHERE  (now() AT TIME ZONE tz)::time BETWEEN '13:00'::time AND '23:00'::time;

    Including upper and lower bounds 1 PM and 11 PM.

    SQL Fiddle

    About AT TIME ZONE: