CODEWITHSUNDEEP
postgresqltimezonetimestamp
Arya
Arya

Reputation: 8965

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?

Upvotes: 3

Views: 80

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656321

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:

Upvotes: 3

Thupten
Thupten

Reputation: 2218

SELECT CONVERT_TZ(FROM_UNIXTIME(1196440219),'GMT','America/Denver');

SELECT * FROM table WHERE DATE_FORMAT(CONVERT_TZ(FROM_UNIXTIME(timestamp_column),'GMT','America/Denver'),'%H') between 13 and 23

Upvotes: 0

Related Questions