Re: Efficient date range search?

SELECT *
FROM pets
WHERE
born <= '2001-07-04 07:01:00+00'
AND
(
died > '2001-07-04 07:01:00+00'
OR
died is NULL
)

Efficient? Well that depends on data distribution, indexes and the 'goodness'
of choice by the planner. One presumes given the data set that can be rewritten
numerous ways to experiment on obtaining the best like spliting each half of
the died test into two queries combined using UNION.

--
Nigel J. Andrews
Director

---
Logictree Systems Limited
Computer Consultants

On Mon, 7 Oct 2002, Jean-Luc Lachance wrote:

> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>