Filtering between certain hours

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Yes the table is very big and yes there are I think 3 indexes on this column.

Thank you

How many rows are returned with: event_date > sydate-7 ?

Try the queries above and see if we need to tweak performance.

If you don't have future dates, it can sometimes be helpful to the optimizer to include an upper bound on the criteria. This would apply to either query posted above

AND event_date < sysdate

ASKER

For sdstuber: Your filter doesn't bring any records

For slightwy: Your filter works partially and I had to modify the hours between 8 and 9 because the query brings also all hours which start with 9. The only problem the filter won't get me events which happen a 10:00 sharp.

Thank you

try:
where to_number(to_char(event_date,'HH24')) >= 8 and
to_number(to_char(event_date,'HH24')) < 10 and
event_date > sydate-7;

>>> For sdstuber: Your filter doesn't bring any records

please post what you did. There's no reason it shouldn't have worked based on what you have described.

here's a simple example to illustrate

WITH yourtable
     AS (    SELECT SYSDATE - 8 + LEVEL * 5 / 1440 event_date
               FROM DUAL
         CONNECT BY SYSDATE - 8 + LEVEL * 5 / 1440 < SYSDATE)
SELECT *
  FROM yourtable
 WHERE event_date BETWEEN TRUNC(event_date) + 8 / 24 AND TRUNC(event_date) + 10 / 24
   AND event_date > SYSDATE - 7

Open in new window

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

The last filter didn't work.
I will try you first filter in the form:
where to_char(event_date,'HH24') >=8 and to_char(event_date,'HH24') <11
and I will let you know later.

Thank you again,

If you want 10:00 AM but not 10:01:

where to_number(to_char(event_date,'HH24')) >= 8 and
to_number(to_char(event_date,'HH24SS')) < 1001 and
event_date > sydate-7;

>> >The last filter didn't work.

what are you calling the "last filter"

>>>> to_number(to_char(event_date,'HH24SS')) < 1001

this doesn't work.

for example: 10:59:00

will have a number of 1000 which is less than 1001.

If you want exactly 10:00:00 then you must extract all 3 units

TO_CHAR(event_date, 'hh24miss')

as I showed above in http:#a40395441

you can compare that directly to a string '100000'
or add an addtional conversion to a number and compare to <= 100000
or just use between

OR

just use the direct date comparison.
if it doesn't work, it's because there's something you're not telling us or not describing correctly.

The logic DOES work, as demonstrated in the example code above.

and, just in case somebody tries it...

to_char(event_date,'HH24MI') - doesn't work either because because

10:00:30 would give a false positive

>>>>>> to_number(to_char(event_date,'HH24SS')) < 1001
I meant minute... typo
to_number(to_char(event_date,'HH24MI')) < 1001

Sorry for the repeat.

>>> I meant minute... typo
>>> to_number(to_char(event_date,'HH24MI')) < 1001

as noted above, that still doesn't work

10:00:30 would give a false positive

>>10:00:30 would give a false positive

You are correct.

ASKER

Thank you again guys.

The accepted answer did get a minority share, but you even stated yourself that it doesn't work. Why was it accepted at all?

ASKER

Sorry for the inconvenient.
It is a mistake, I didn't know the solutions would receive a different status.
Thank you again,

ASKER

Thank you again

I see you changed the originally accepted answer to an assist, but since it doesn't work, I'm still not sure why it's being marked as part of the solution.