Re: Efficient date range search?

From: Jonathan Leffler <jleffler_at_earthlink.net>
Date: Tue, 08 Oct 2002 05:18:29 GMT
Message-ID: <3DA26A99.3000607@earthlink.net>

Pablo Sanchez wrote:

> Kieran <kieran_at_dunelm.org.uk> wrote:

>>Pablo Sanchez wrote:
>>>Pablo Sanchez <pablo_at_dev.null> wrote:
>>>>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.

Of course - predicting when babies are going to be born is totally haphazard,in general; they're late, or early, but not on time - especially not to the minute, let alone the second. If the cut-off time is an arbitrary date in the past, though, you have to exclude all the critters born after the cut-off date, whether they are alive or not, just as you have to exclude all critters that died before the cut-off date.

Actually, a better representation for "not died" is a date in the indefinite future - 9999-12-31 23:59:59 perhaps. Yes, you have to worry about the Y10K problem (see RFC 2550 - http://www.faqs.org/rfcs/rfc2550.html) but I plan to take that issue up in 5001-01-01 and no sooner. :-)

Then the criterion for 'alive at time 2001-07-04 19:30:00' is:

WHERE born <= TIMESTAMP '2001-07-04 19:30:00'

   AND died >= TIMESTAMP '2001-07-04 19:30:00'

In standard SQL, it gets trickier to express things with one minute granularity. Informix happens to handle that explicitly with a data type (peculiar to Informix) of DATETIME YEAR TO MINUTE.

The standard reference, at least pending C J Date's book on Temporal Databases due out in November, is R T Snodgrass's book "Developing Time-Oriented Database Applications in SQL".

-- 
Jonathan Leffler                   #include <disclaimer.h>
Email: jleffler_at_earthlink.net, jleffler_at_us.ibm.com
Guardian of DBD::Informix 1.00.PC2 -- http://dbi.perl.org/

Received on Tue Oct 08 2002 - 00:18:29 CDT