pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTIME ZONE SQL
7+ messages / 4 participants
[nested] [flat]
* TIME ZONE SQL
@ 2004-02-04 17:57 Raman <ramang@smartdatainc.com>
2004-02-04 20:03 ` Re: TIME ZONE SQL Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 7+ messages in thread
From: Raman @ 2004-02-04 17:57 UTC (permalink / raw)
To: pgsql-sql
Hi Pls see this Query
I have following fields in my table "customer_events"
a) time_difference (which has values like -05:00 , +05:30, +00:00 etc)
b) start_time (has value like 11:05, 10:00 etc)
c) send_before_time (has value like 00:05, 00:10 etc)
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),
CURRENT_TIME(0) AT TIME ZONE "interval" (time_difference),
(start_time::time - send_before_time::time) as difference,
time_difference
from
customer_events
MY PROBLEM:
This query runs fine when i have
time_difference value like +5:30 +5:00 i.e. works fine for positive values
but failes for negative values i.e. -7:00, -6:00 etc
I don't know WHY WHY... pls help
I am helpless.
do reply back....
Regards,
Raman Garg
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: TIME ZONE SQL
2004-02-04 17:57 TIME ZONE SQL Raman <ramang@smartdatainc.com>
@ 2004-02-04 20:03 ` Richard Huxton <dev@archonet.com>
2004-02-05 08:28 ` Re: TIME ZONE SQL Raman <ramang@smartdatainc.com>
0 siblings, 1 reply; 7+ messages in thread
From: Richard Huxton @ 2004-02-04 20:03 UTC (permalink / raw)
To: Raman <ramang@smartdatainc.com>; pgsql-sql
On Wednesday 04 February 2004 17:57, Raman wrote:
>
> This query runs fine when i have
> time_difference value like +5:30 +5:00 i.e. works fine for positive values
> but failes for negative values i.e. -7:00, -6:00 etc
>
> I don't know WHY WHY... pls help
> I am helpless.
Can you give example outputs? It's difficult to decide otherwise.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: TIME ZONE SQL
2004-02-04 17:57 TIME ZONE SQL Raman <ramang@smartdatainc.com>
2004-02-04 20:03 ` Re: TIME ZONE SQL Richard Huxton <dev@archonet.com>
@ 2004-02-05 08:28 ` Raman <ramang@smartdatainc.com>
2004-02-05 11:00 ` Re: TIME ZONE SQL Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 7+ messages in thread
From: Raman @ 2004-02-05 08:28 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; pgsql-sql
Hi Richard,
Follwing are the Results that I get
Lets say I am in IST (Indian standart time) 15:00:00 hrs
so equivalent time at US Mountain (-7:00) is 02:30:00 hrs
and equivalent time at Japan(+9:00) is 18:30:00hrs
NOW WHAT I have is this
I have following fields in my table "customer_events"
a) time_difference (which has values like +09:00 , -7:00, +00:00 etc)
b) start_time (has value like 11:00:00 , 10:00:00 etc)
c) send_before_time (has value like 00:15:00 , 00:07:00 etc)
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),
CURRENT_TIME(0) AT TIME ZONE "interval" (time_difference),
(start_time::time - send_before_time::time) as difference,
time_difference
from
customer_events
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)
it returns True (YES) when time_difference value are like +5:30 +5:00 i.e.
works fine for positive values
but failes for negative values i.e. -7:00, -6:00 time Zone values and
returns me FALSE.
So as per upper example
lets say for an Japanese event "start_time=18:34:00" and
"send_before_time="00:05:00" my above "between" query return true as
current_time at japan zone (18:30) lies between thtat
but for US Mountain event at "start_time=02:34:00" and
"send_before_time=00:05:00" above "between" query return FALSE and
current_time at US mountain zone (02:30) lies between that
I don't know WHY
Pls help.. in this. Also pls let me know if you need any other information.
With Regards,
Raman Garg
-- 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 1:33 AM
Subject: Re: [SQL] TIME ZONE SQL
> On Wednesday 04 February 2004 17:57, Raman wrote:
> >
> > This query runs fine when i have
> > time_difference value like +5:30 +5:00 i.e. works fine for positive
values
> > but failes for negative values i.e. -7:00, -6:00 etc
> >
> > I don't know WHY WHY... pls help
> > I am helpless.
>
> Can you give example outputs? It's difficult to decide otherwise.
> --
> Richard Huxton
> Archonet Ltd
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: TIME ZONE SQL
2004-02-04 17:57 TIME ZONE SQL Raman <ramang@smartdatainc.com>
2004-02-04 20:03 ` Re: TIME ZONE SQL Richard Huxton <dev@archonet.com>
2004-02-05 08:28 ` Re: TIME ZONE SQL Raman <ramang@smartdatainc.com>
@ 2004-02-05 11:00 ` Richard Huxton <dev@archonet.com>
2004-02-05 14:59 ` Re: TIME ZONE SQL Raman Garg <ramang@smartdatainc.com>
0 siblings, 1 reply; 7+ messages in thread
From: Richard Huxton @ 2004-02-05 11:00 UTC (permalink / raw)
To: Raman <ramang@smartdatainc.com>; 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,
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: TIME ZONE SQL
2004-02-04 17:57 TIME ZONE SQL Raman <ramang@smartdatainc.com>
2004-02-04 20:03 ` Re: TIME ZONE SQL Richard Huxton <dev@archonet.com>
2004-02-05 08:28 ` Re: TIME ZONE SQL Raman <ramang@smartdatainc.com>
2004-02-05 11:00 ` Re: TIME ZONE SQL Richard Huxton <dev@archonet.com>
@ 2004-02-05 14:59 ` Raman Garg <ramang@smartdatainc.com>
2004-02-05 16:31 ` Re: TIME ZONE SQL Richard Huxton <dev@archonet.com>
2004-02-05 16:54 ` Re: TIME ZONE SQL Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 7+ messages in thread
From: Raman Garg @ 2004-02-05 14:59 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; 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)
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@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
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: TIME ZONE SQL
2004-02-04 17:57 TIME ZONE SQL Raman <ramang@smartdatainc.com>
2004-02-04 20:03 ` Re: TIME ZONE SQL Richard Huxton <dev@archonet.com>
2004-02-05 08:28 ` Re: TIME ZONE SQL Raman <ramang@smartdatainc.com>
2004-02-05 11:00 ` Re: TIME ZONE SQL Richard Huxton <dev@archonet.com>
2004-02-05 14:59 ` Re: TIME ZONE SQL Raman Garg <ramang@smartdatainc.com>
@ 2004-02-05 16:31 ` Richard Huxton <dev@archonet.com>
1 sibling, 0 replies; 7+ messages in thread
From: Richard Huxton @ 2004-02-05 16:31 UTC (permalink / raw)
To: Raman Garg <ramang@smartdatainc.com>; pgsql-sql
On Thursday 05 February 2004 14:59, Raman Garg wrote:
> 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)
My point is that subtracting one time from another does not give you a time.
3:30pm (time) - 2:00pm (time) = 1h30m (interval)
If "send_before_time" is an interval (e.g. '1 hour and 30 minutes') you should
probably make it an interval.
If it is in fact a time (e.g. '2:00 pm') then it doesn't make sense to
subtract it.
Even if your solution works, I'd recommend getting your types right - it'll
save effort later.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: TIME ZONE SQL
2004-02-04 17:57 TIME ZONE SQL Raman <ramang@smartdatainc.com>
2004-02-04 20:03 ` Re: TIME ZONE SQL Richard Huxton <dev@archonet.com>
2004-02-05 08:28 ` Re: TIME ZONE SQL Raman <ramang@smartdatainc.com>
2004-02-05 11:00 ` Re: TIME ZONE SQL Richard Huxton <dev@archonet.com>
2004-02-05 14:59 ` Re: TIME ZONE SQL Raman Garg <ramang@smartdatainc.com>
@ 2004-02-05 16:54 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 7+ messages in thread
From: Tom Lane @ 2004-02-05 16:54 UTC (permalink / raw)
To: Raman Garg <ramang@smartdatainc.com>; +Cc: Richard Huxton <dev@archonet.com>; pgsql-sql
"Raman Garg" <ramang@smartdatainc.com> writes:
> 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:
I suspect that this revised clause will give you problems too, namely
selecting rows you don't want.
I think what may actually be happening here is that you have times
wrapping around past midnight. For instance consider
regression=# select '10:30'::time + '15 hours'::interval;
?column?
----------
01:30:00
(1 row)
regression=# select '10:30'::time - '15 hours'::interval;
?column?
----------
19:30:00
(1 row)
It seems to me that your approach to the problem is all wrong, and you
need to be using timestamp-based calculations not time-of-day-based
calculations.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2004-02-05 16:54 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-02-04 17:57 TIME ZONE SQL Raman <ramang@smartdatainc.com>
2004-02-04 20:03 ` Richard Huxton <dev@archonet.com>
2004-02-05 08:28 ` Raman <ramang@smartdatainc.com>
2004-02-05 11:00 ` Richard Huxton <dev@archonet.com>
2004-02-05 14:59 ` Raman Garg <ramang@smartdatainc.com>
2004-02-05 16:31 ` Richard Huxton <dev@archonet.com>
2004-02-05 16:54 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox