Re: Efficient date range search?

If the pet is still alive today died would be NULL and the where clause
would not be true.

How about this:

On insert to pets, set the date to 9999-12-31.
On the deth of a pet update the died field.

Create an index on died.

select * from pets where died > {whatever date}

will return the pets that were alive on that date.


JLL


Shridhar Daithankar wrote:
>
> On 4 Oct 2002 at 23:35, mvh@ix.netcom.com wrote:
>
> > CREATE TABLE "pets" (
> >       name VARCHAR(20);
> >       "born" timestamp;
> >       "died" timestamp;
> > );
> >
> > and I have a LOT of pets (let's say millions) and some don't live too
> > long (mice, fruitflies, whatever), and some do (parrots, elephants).
> >
> > I would like to make a query to say
> >
> > on july 4 of last year, what pets were alive?
> >
> > and I would like to make this query right to the minute
> >
> > on july 4 of last year at 7:01 PM what pets were alive?
>
> Create an index on died field. And query like
>
> select * from pets where died < "last year july 4 7:01 PM;
>
> These will be alive pets then.. Should be pretty efficient.
>
> Bye
>  Shridhar
>
> --
> QOTD:   Money isn't everything, but at least it keeps the kids in touch.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org