General
cancel
Showing results for 
Search instead for 
Did you mean: 

Problem via date and time field in Oracle HP UX

SOLVED
Go to solution
DnD_1
Regular Advisor

Problem via date and time field in Oracle HP UX

Hi guys,

I tried to look for particular data but realized that our date field is time included.

Example :

Select count(*) from CDR where date='26-feb-2009';

The result :

count(*)
5

It will only count the row with date 26-feb-2009 12:00 AM, and not the others row. The date format look like this

Date
-------
26-feb-2009 12:00 AM

This is wrong because we have tons of CDR, something like 5000 or more for an hour.

and I tried to used

Select count(*) from CDR where date='26-feb-2009%';

or

Select count(*) from CDR where date like '26-feb-2009%';

Both took really long time to response!

Now, please anyone can guide how to select the date field which it will count the whole date for 24 hours ?

Sorry for my bad english :)


4 REPLIES
Laurent Menase
Honored Contributor
Solution

Re: Problem via date and time field in Oracle HP UX

try
select count(*) where date >='26-feb-2009' and date <'27-feb-2009';
Laurent Menase
Honored Contributor

Re: Problem via date and time field in Oracle HP UX

string matching is always very costly.
Yogeeraj_1
Honored Contributor

Re: Problem via date and time field in Oracle HP UX

hi,

the correct way would be:

Select count(*) from CDR where trunc(date)='26-feb-2009';

hope this helps!

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Hein van den Heuvel
Honored Contributor

Re: Problem via date and time field in Oracle HP UX



Laurent>> string matching is always very costly.

A little more CPU, but is would not cause 'a really long time to respond'

There must have been an index on that column, and this particular string match cause Oracle no longer to be able to use the index. The reason is that the algorithm for this string match is surely a sub-string function. Well, imagine the query had been "like 26%" or "like 26%-feb%". Clearly for those queries Oracle must read all rows.
You and I know that when the full date is specified, that it could have used the (binary) date column, but apparently the optimizer does not realize this subtle threshhold because it is thinking string, not date.

fwiw,
Hein.