From: Richard Huxton <dev@archonet.com>
To: "Raman" <ramang@smartdatainc.com>, "pgsql-sql" <pgsql-sql@postgresql.org>
Subject: Re: TIME ZONE SQL
Date: Thu, 5 Feb 2004 11:00:58 +0000
User-Agent: KMail/1.5
References: <JEEKIPNAKJNCFLMOBKHGOEDKDCAA.email@juergen-cappel.de>
	<200402042003.07189.dev@archonet.com>
	<006101c3ebc2$134c0dc0$d4c7a8c0@raman>
In-Reply-To: <006101c3ebc2$134c0dc0$d4c7a8c0@raman>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200402051100.58825.dev@archonet.com>

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