Geek asks and answers

Postgresql: timezone sensitive query, timestamp without timezone

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

Request data for the time range (i.e., from 8:00 to 16:00) in the time zone America/New_York.

Is there an easy way to achieve this?

thanks p.

+3
source share
2 answers

Figured it out.

You need to first convert the time into it with time zoneversion ie my_ts at time zone 'Australia/Sydney', and then convert it to it through NYT viaat 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
+3
source

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

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

, :

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

, , , .

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

More articles:


All Articles