Re: Datetime column index access
"Art S. Kagel" <kagel@bloomberg.net> wrote in message news:<pan.2004.01.21.18.00.02.123204.12806@bloomberg.net>...
> On Wed, 21 Jan 2004 14:21:27 -0500, Octavio G'mez wrote:
>
> try:
>
> Select * from table1
> where
> date_a between extend(today, year to second)
> and extend((today+1), year to second);>
> Art S. Kagel
>
I hate to even do this because looking through comp.databases.informix
I can see that Art is really helpful and really good, but ( sinister
music plays ) this is wrong because between is inclusive on both ends.
So you could have problems with futured dated stuff. So you would get
all of today and tomorrow at midnight. ie assuming today is 2004-01-22
then 2004-01-22 5:00:00, 2004-01-22 6:00:00 and 2004-01-23 00:00:00
whould all be returned when only the first to should be returned.
better would be
select
*
from
table1
where
date_a >= extend(today, year to second) and
date_a < extend( today+1 year to second)
;
Note clever use of "less-than" symbol ;-).
Basically any function on date_a side will sequential scan. If you can
put the function manipulations on the constant side then you are good.
And if you are using anything below 9.3 don't use functional indexes
because I have found them to be quite unreliable. Plus they would be
slightly slower than the above.
On Thu, 22 Jan 2004 12:23:58 -0500, Curtis Crowson wrote:
> "Art S. Kagel" <kagel@bloomberg.net> wrote in message
> news:<pan.2004.01.21.18.00.02.123204.12806@bloomberg.net>...
>> On Wed, 21 Jan 2004 14:21:27 -0500, Octavio G'''mez wrote:
>>
>> try:
>>
>> Select * from table1
>> where
>> date_a between extend(today, year to second)
>> and extend((today+1), year to second);>>
>> Art S. Kagel
>>
>>
> I hate to even do this because looking through comp.databases.informix I can
> see that Art is really helpful and really good, but ( sinister music plays)
I promise, I'll only pout for a bit. ;-{
> this is wrong because between is inclusive on both ends. So you could have
> problems with futured dated stuff. So you would get all of today and
> tomorrow at midnight. ie assuming today is 2004-01-22 then 2004-01-22
> 5:00:00, 2004-01-22 6:00:00 and 2004-01-23 00:00:00 whould all be returned
> when only the first to should be returned.
You are of course correct. The principle holds but the example is definitely
flawed.
> better would be
>
> select
> *
> from
> table1
> where
> date_a >= extend(today, year to second) and date_a < extend( today+1 year
> to second)
> ;
OR
SELECT *
FROM table1
WHERE date_a BETWEEN extend(today, year to second)
AND (extend((today+1), year to second) - 1 units second);
Art S. Kagel
> Note clever use of "less-than" symbol ;-).
>
> Basically any function on date_a side will sequential scan. If you can put
> the function manipulations on the constant side then you are good.
>
> And if you are using anything below 9.3 don't use functional indexes because
> I have found them to be quite unreliable. Plus they would be slightly slower
> than the above.