Re: Efficient date range search?

From: Kieran <kieran_at_dunelm.org.uk>
Date: Mon, 07 Oct 2002 20:21:14 +0100
Message-ID: <3DA1DEAA.8030005@dunelm.org.uk>

Pablo Sanchez wrote:
> Pablo Sanchez <pablo_at_dev.null> wrote in
> news:Xns92A07011F6EE7pingottpingottbah_at_209.189.89.243:
>
>

>>Assuming you're using one of the three, the query would be:
>>
>>    select count(*) from pet where died <= "date/time value"
>>

>
>
> Forgot a case... the above only handles when a pet has died and you've
> stamped the row with the date of their ... expiration. when a pet is
> still alive, the assumption is that 'died' will be set to NULL and we
> need that in the predicate:
>
> SELECT count(*)
> FROM pet
> WHERE died <= <<date/time value>>
> OR died IS NULL
>
> Sorry for any confusion.

Unfortunately, this will count pets that haven't been born by the date in question.

The obvious query will be:

select * from pet where my_date > born and (my_date < died or died is null)

See my other post below for some thoughts on how an RDBMS might optimise this.

Regards,
Kieran Elby Received on Mon Oct 07 2002 - 14:21:14 CDT