interval question

On Mon, Mar 05, 2001 at 09:59:40PM -0300, Fernando P. Schapachnik wrote:

Hello:
Maybe somebody on the list can give me a hand with this. I
have:

id serial,
start time,
duration time

(That is, ids have an start time and a duration).

I want to know which ids are current, ie, which satisfy
start<=now<=start+duration.

The problem is that start+duration is an interval, and I
can't find the way to cast now to interval or interval to time or
tinterval in order to make the comparison.

Maybe something like:

CREATE start_stop (
id SERIAL,
start DATETIME,
stop DATETIME
);

INSERT INTO start_stop (start, stop) VALUES (now(), now() + '1:00');

SELECT id from start_stop WHERE now() BETWEEN start AND stop;

--
Eric G. Miller <egm2@jps.net>

At 21:59 05.03.2001 -0300, Fernando P. Schapachnik wrote:

[...]
I want to know which ids are current, ie, which satisfy
start<=now<=start+duration.

The problem is that start+duration is an interval, and I
can't find the way to cast now to interval or interval to time or
tinterval in order to make the comparison.

Any ideas?

Yes, maybe...

'0:00'+start <= '0:00'+now <= start+duration

It is not really beautiful, and I have not tested it, but if it works: The
are no awards for beauty, are there?

Stefan

On 3/6/01, 12:59:40 AM, Fernando "P." Schapachnik
<fernando@schapachnik.com.ar> wrote regarding [GENERAL] interval question:

Hello:
Maybe somebody on the list can give me a hand with this. I
have:
id serial,
start time,
duration time

Surely "duration" should be of type interval.
Also - are you sure "start" should be time and not timestamp - depends if
you don't care about dates.

(That is, ids have an start time and a duration).

I want to know which ids are current, ie, which satisfy
start<=now<=start+duration.

SELECT id FROM foo WHERE start<=now() AND now<=(start + duration);

The problem is that start+duration is an interval, and I
can't find the way to cast now to interval or interval to time or
tinterval in order to make the comparison.

The cast should happen automatically. If not, you can do things like:

select now() + '10:00'::interval;
select '2001-03-13'::timestamp;

to convert into interval or a timestamp.

En un mensaje anterior Stefan Waidele jun. escribi�:

At 21:59 05.03.2001 -0300, Fernando P. Schapachnik wrote:

[...]
I want to know which ids are current, ie, which satisfy
start<=now<=start+duration.

The problem is that start+duration is an interval, and I
can't find the way to cast now to interval or interval to time or
tinterval in order to make the comparison.

Any ideas?

Yes, maybe...

'0:00'+start <= '0:00'+now <= start+duration

When I do:

select * from table where '0:00'::time+'now'<=start+duration

I lost conection to the backend. Why?

Fernando P. Schapachnik
fernando@schapachnik.com.ar

En un mensaje anterior dev@archonet.com escribi�:

On 3/6/01, 12:59:40 AM, Fernando "P." Schapachnik
<fernando@schapachnik.com.ar> wrote regarding [GENERAL] interval question:

Hello:
Maybe somebody on the list can give me a hand with this. I
have:
id serial,
start time,
duration time

Surely "duration" should be of type interval.
Also - are you sure "start" should be time and not timestamp - depends if
you don't care about dates.

(That is, ids have an start time and a duration).

I want to know which ids are current, ie, which satisfy
start<=now<=start+duration.

SELECT id FROM foo WHERE start<=now() AND now<=(start + duration);

It worked fine! Thanks!

Fernando P. Schapachnik
fernando@schapachnik.com.ar