From: Richard Huxton <dev@archonet.com>
To: Raman <ramang@smartdatainc.com>
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: TIME ZONE SQL
Date: Thu, 5 Feb 2004 11:00:58 +0000
Message-ID: <200402051100.58825.dev@archonet.com> (raw)
In-Reply-To: <006101c3ebc2$134c0dc0$d4c7a8c0@raman>
References: <JEEKIPNAKJNCFLMOBKHGOEDKDCAA.email@juergen-cappel.de>
<200402042003.07189.dev@archonet.com>
<006101c3ebc2$134c0dc0$d4c7a8c0@raman>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, ramang@smartdatainc.com
Subject: Re: TIME ZONE SQL
In-Reply-To: <200402051100.58825.dev@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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