From: | Jean-Luc Lachance <jllachan(at)nsd(dot)ca> |
---|---|
To: | shridhar_daithankar(at)persistent(dot)co(dot)in |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Efficient date range search? |
Date: | 2002-10-07 16:11:35 |
Message-ID: | 3DA1B237.44FF1696@nsd.ca |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2002-10-07 16:20:37 | Re: Efficient date range search? |
Previous Message | Zeugswetter Andreas SB SD | 2002-10-07 15:42:12 | Re: [HACKERS] Large databases, performance |