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(at)ix(dot)netcom(dot)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(at)postgresql(dot)org