postgresql: timezone sensitive query on a timestamp without timezone

Question

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 (ie 8am-4pm) in America/New_York time zone.

Is there an easy way to achieve this?

thanks, p.

Answer 1

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