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
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
