Re: TIME ZONE SQL - Mailing list pgsql-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,
PRIMARYKEY (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.sqlyesno |   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:00f     | 10:54:29+00 | 07:54:29-03        | 01:15    | 02:00:00 | -03:00f     |
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:00t     | 10:54:29+00 | 07:54:29-03
| 10:15:00 | 11:00:00 | -03:00f     | 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