postgresql: timezone sensitive query on a timestamp without timezone

I have a table with a timestamp without time zone column (data entered is assumed to be in Australia/Sydney time zone).

Query on data for a time range (i开发者_StackOverflowe 8am-4pm) in America/New_York time zone.

Is there an easy way to achieve this?

thanks, p.

Figured it out.

You need to first convert the time to it's with time zone version ie my_ts at time zone 'Australia/Sydney' and then convert that to it's NY counterpart via at time zone 'America/New_York'

select
    my_ts as "Default(syd)",
    my_ts at time zone 'Australia/Sydney' as "SYD",
    my_ts at time zone 'Australia/Sydney' at time zone 'America/New_York' as "NY",
    date_part('hour', my_ts at time zone 'Australia/Sydney' at time zone 'America/New_York') as "NY-hr"
from my_table
where date_part('hour', my_ts at time zone 'Australia/Sydney' at time zone 'America/New_York')>=8
    and date_part('hour', my_ts at time zone 'Australia/Sydney' at time zone 'America/New_York')<16

You can convert everything to the same time zone so you can compare them with (if the timezone was set):

select current_time, current_time at time zone 'gmt';
      timetz       |     timezone      
-------------------+-------------------
 20:50:51.07742-07 | 03:50:51.07742+00

If the time zone is not set and you need to correct it some local time:

select now()::time, now()::time + '+8:00'::interval;
       now       |    ?column?     
-----------------+-----------------
 20:57:49.420742 | 04:57:49.420742

Once you get the time the way you want, just the extract the hour and you can use a simple condition to select the proper times.

select * 
  from 
  (select extract(hour from now()::time + '+8:00'::interval) as hour) as t 
  where hour between 8 and 16;