Re: TIME ZONE SQL - Mailing list pgsql-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)andstart_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@archonet.com>
To: "Raman" <ramang@smartdatainc.com>; "pgsql-sql"
<pgsql-sql@postgresql.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
>