CODEWITHSUNDEEP
postgresqltimezonetimestamp
pstanton
pstanton

Reputation: 36640

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

Is there an easy way to achieve this?

thanks, p.

Upvotes: 3

Views: 5732

Answers (2)

pstanton
pstanton

Reputation: 36640

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

Upvotes: 3

nate c
nate c

Reputation: 9005

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;

Upvotes: 0

Related Questions