3

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?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Arya
  • 8,473
  • 27
  • 105
  • 175

2 Answers2

3

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.

Details for AT TIME ZONE:

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
0

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

Thupten
  • 2,158
  • 1
  • 24
  • 31
  • I want the rows that their current time of the day according to the timezone is between 1 PM to 11 PM – Arya Aug 20 '15 at 23:37