Re: Efficient date range search?

mvh_at_ix.netcom.com wrote in news:opdsna.i6e1.ln_at_netcom1.netcom.com:

> Does anybode know a good (efficient) algorithm for the following?
>
> Imagine that I have a lot of entries of the form (sorry if the SQL
> is messed up):
>
> 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?
>
> I can't figure out how to index or query this in a manner that
> isn't going to devolve into a linear search, which would be too
> slow.

You don't state which RDBMS you're using. Oracle, Sybase ASE and MS SQL all provide resolution, at least, down to the second with a 'date time' type of datatype so if you use one of those, you're okay.

Assuming you're using one of the three, the query would be:

    select count(*) from pet where died <= "date/time value"

I'll assume that 'died' is a NULL'able column and is set to NULL when the pet is still alive. Your index would be on the 'died' column.

-- 
Pablo Sanchez, High-Performance Database Engineering
http://www.hpdbe.com