Re: TIME ZONE SQL

On Thursday 05 February 2004 08:28, Raman wrote:
> Hi Richard,
> Follwing are the Results that I get

> WHEN I run "between" query like
>
> ((CURRENT_TIME(0) AT TIME ZONE "interval" (time_difference)) BETWEEN
> (start_time::time - send_before_time::time)
> and start_time::time)

I think the issue is the "send_before_time" - I think this should be an
interval rather than a time. I'm assuming it means something like "send
warning message X hours before ..."

Using the SQL below (your test data might need different values):

CREATE TABLE tztest (
id serial,
time_difference interval,
start_time time,
send_before_time time,
PRIMARY KEY (id)
);

COPY tztest(time_difference,start_time,send_before_time) FROM stdin;
-03 01:00 00:45
-03 02:00 00:45
-03 03:00 00:45
-03 04:00 00:45
-03 05:00 00:45
-03 06:00 00:45
-03 07:00 00:45
-03 08:00 00:45
-03 09:00 00:45
-03 10:00 00:45
-03 11:00 00:45
-03 12:00 00:45
-03 13:00 00:45
-03 14:00 00:45
-03 15:00 00:45
-03 16:00 00:45
-03 17:00 00:45
-03 18:00 00:45
-03 19:00 00:45
-03 20:00 00:45
-03 21:00 00:45
\.

select
((CURRENT_TIME(0) AT TIME ZONE "interval" (time_difference)) BETWEEN
(start_time::time - send_before_time::time)
and start_time::time) as yesno,

current_time(0) AS curr_tm,
CURRENT_TIME(0) AT TIME ZONE "interval" (time_difference) AS
curr_with_timediff,

(start_time::time - send_before_time::time) as start_pt,
start_time AS end_pt,
time_difference
from
tztest;

-- Notice how we use send_before_time as an interval here
--
select
((CURRENT_TIME(0) AT TIME ZONE "interval" (time_difference)) BETWEEN
(start_time::time - send_before_time::interval)
and start_time::time) as yesno,

current_time(0) AS curr_tm,
CURRENT_TIME(0) AT TIME ZONE "interval" (time_difference) AS
curr_with_timediff,

(start_time::time - send_before_time::interval) as start_pt,
start_time AS end_pt,
time_difference
from
tztest;

-- END SQL --

Gives the following results:
richardh=# \i timezone_test.sql
yesno | curr_tm | curr_with_timediff | start_pt | end_pt |
time_difference
-------+-------------+--------------------+----------+----------+-----------------
f | 10:54:29+00 | 07:54:29-03 | 00:15 | 01:00:00 | -03:00
f | 10:54:29+00 | 07:54:29-03 | 01:15 | 02:00:00 | -03:00
f | 10:54:29+00 | 07:54:29-03 | 02:15 | 03:00:00 | -03:00
...etc...
f | 10:54:29+00 | 07:54:29-03 | 20:15 | 21:00:00 | -03:00
(21 rows)

yesno | curr_tm | curr_with_timediff | start_pt | end_pt |
time_difference
-------+-------------+--------------------+----------+----------+-----------------
f | 10:54:29+00 | 07:54:29-03 | 00:15:00 | 01:00:00 | -03:00
...etc...
f | 10:54:29+00 | 07:54:29-03 | 09:15:00 | 10:00:00 | -03:00
t | 10:54:29+00 | 07:54:29-03 | 10:15:00 | 11:00:00 | -03:00
f | 10:54:29+00 | 07:54:29-03 | 11:15:00 | 12:00:00 | -03:00
...etc...
f | 10:54:29+00 | 07:54:29-03 | 20:15:00 | 21:00:00 | -03:00
(21 rows)

Notice the difference between start_pt in each case. In the first example,
time - time = difference, wheras in the second time - difference = time

Does that help out at your end?
--
Richard Huxton
Archonet Ltd