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_at_dunelm.org.uk>
>
>
> 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: Mon, 07 Oct 2002 20:21:14 +0100
Message-ID: <3DA1DEAA.8030005_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.
Regards,
Kieran Elby
Received on Mon Oct 07 2002 - 21:21:14 CEST