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.
-
I think you mean time without timestamp. timezone as a data type only exists in relation to time. (you could have an interval column that you could add to the times to compare them together though.)nate c– nate c2011-03-15 05:06:40 +00:00Commented Mar 15, 2011 at 5:06
Add a comment
|
2 Answers
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
2 Comments
nate c
If I add 3 hours to now() which would be the time in New York for me then do how you did it:
select now()::time, ((now()::time + '3:00':: interval) at time zone 'America/Vancouver' at time zone 'America/New_York')::time; My default zone is America/Vancouver. This gives me times that are 6 hours apart. It has doubled the difference not equalized them.
pstanton
maybe
now() is returning a ts with tz already? i don't know but my query works 100% for me, i've tested it thoroughly, whereas yours is not applicable to my problem.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;
3 Comments
pstanton
but how do i query on a time range ie 08:00 - 16:00 where the time range is in a different time zone?
pstanton
thanks but you are not answering my question... maybe i didn't explain properly - i'm dealing with real dates on a system which is in Sydney tz/locale.
nate c
Maybe I did it explain it properly but the answer is in the first line. You would be much better off changing your column to one with time zone and store that (since you already know somehow that came from New York.) Then change the relevant to the proper timezones. Once you do that your problems vanish as PG will just do the right thing and all your conversions disappear. current_time and now() and just as real as your dates, they are just used as examples.
