Re: TIME ZONE SQL

Hi Richard,

What I am having is

CREATE TABLE "customer_events" (
"event_id" numeric (10) NOT NULL,
"customer_id" numeric (10) NOT NULL,
"event_name" varchar (100) ,
"event_datetime" date ,
"start_time" time ,
"repeat_untill_date" date ,
"send_before_time" time,
"time_difference" time
PRIMARY KEY ("event_id"))

So my "send_before_time" is of datatype "time" only as you have assumed.
only difference was, In your table you are having time_difference field as
"interval" type.

I am subtracting time field from a time field. i.e. start_time::time -
send_before_time::time
so creating (start_time::time - send_before_time::time) as start_pt is not
making any difference at my side.
(I hope I am getting your point what you have explained)

Anyways i got a solution in this way..
Actually my "between" is creating some problems and is not giving me results
so what I have done is . IN MY WHERE CLAUSE OF QUERY:

Where ((CURRENT_TIME(0) AT TIME ZONE "interval" (time_difference)) BETWEEN
(start_time::time - send_before_time::time)
and start_time::time)
OR
((CURRENT_TIME(0) AT TIME ZONE "interval" (time_difference)) BETWEEN
(start_time::time and (start_time::time - send_before_time::time))

--> check the difference two between
now one of my results work for positive time zone(+5:30) and another for
negative time zone(-7:00)

Well, it worked for me now.. maybe some logic of neagative time zone is
there due to which our time calculation make the difference of two time
greater. :-?

Thanks for your descriptive and nice explanation...

Regards,
-- Raman

----- Original Message -----
From: "Richard Huxton" <dev(at)archonet(dot)com>
To: "Raman" <ramang(at)smartdatainc(dot)com>; "pgsql-sql"
<pgsql-sql(at)postgresql(dot)org>
Sent: Thursday, February 05, 2004 4:30 PM
Subject: Re: [SQL] 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>