Re: Efficient date range search?
From: Pablo Sanchez <pablo_at_dev.null>
Date: 7 Oct 2002 14:39:26 -0500
Message-ID: <Xns92A08BC9C5E3Apingottpingottbah_at_209.189.89.243>
>> Pablo Sanchez <pablo_at_dev.null> wrote in
>> news:Xns92A07011F6EE7pingottpingottbah_at_209.189.89.243:
>>
>>
>>
>>
>> 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.
Date: 7 Oct 2002 14:39:26 -0500
Message-ID: <Xns92A08BC9C5E3Apingottpingottbah_at_209.189.89.243>
Kieran <kieran_at_dunelm.org.uk> wrote in news:3DA1DF00.7080004_at_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.
Perhaps a bad assumption on my part but I was assuming that we would only populate the table with pets that have been born. If my assumption is wrong, then you're totally right, I need to add the 'born' column in the predicate.
Thx!
-- Pablo Sanchez, High-Performance Database Engineering http://www.hpdbe.comReceived on Mon Oct 07 2002 - 21:39:26 CEST