Postgres Pro
Downloads
Home   >   mailing lists

Thread: interval question

interval question

From
"Fernando P. Schapachnik"
Date:
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.

    Any ideas?

    Thanks in advance.

Fernando P. Schapachnik
fernando@schapachnik.com.ar

Re: interval question

From
"Stefan Waidele jun."
Date:
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

Re: interval question

From
dev@archonet.com
Date:
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.

Re: Re: interval question

From
"Fernando P. Schapachnik"
Date:
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

Re: interval question

From
"Eric G. Miller"
Date:
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>

Re: interval question

From
"Fernando P. Schapachnik"
Date:
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