Select items where local time is ~8AM.

Question

I have a table called items. It contains a column tz with timezone identifiers (like America/New_York, Europe/London, etc).

I want to select all the items where the current time is 8AM +/- 5 minutes.

So if I run the query at 8AM EST it will return rows where tz = 'America/New_York'.
If I run the query at 9AM EST it will return rows where tz = 'America/Chicago'.

Answer 1

You should be storing the offset for each timezone. Then the query is as simple as

select * from items where GETDATE() between 
   DATEADD(minute,5,DATEADD(HOUR,offset,GETUTCDATE())) and   
   DATEADD(minute,-5,DATEADD(HOUR,offset,GETUTCDATE()))

I'm using SqlServer GETUTCDATE and DATEADD functions, but you can easily make them work in postgresql if you lookup the correct function names.

EDIT If you can't add a offset to the table, create a timezone table with the string timezone and numeric offset. Join and run the above query.

Answer 2

Your tz field is valid if it respects this: http://www.postgresql.org/docs/8.0/static/datetime-keywords.html#DATETIME-TIMEZONE-SET-TABLE

If so, I think this should work (no tested):

SELECT * 
FROM items 
WHERE LOCALTIMESTAMP BETWEEN 
(LOCALTIMESTAMP AT TIME ZONE tz) - interval '5 minutes' AND 
(LOCALTIMESTAMP AT TIME ZONE tz) + interval '5 minutes'