Filtering between certain hours

Hi guys,

I have a column named "Event date" and I would like to query all events that happened between 8 and 10 in the morning every day in the last week.
Can anyone tell me what is the syntax of the filter to do this ?

Thank you,
marian68Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Do you have an index on the date column?

If not or the table is small:

where to_char(event_date,'HH24') between 8 and 10 and event_date > sydate-7;

sysdate-7 is 7 days ago to the second.

If the table is large and there is an index on the column, let us know and we can tweak it to the index can be used.
sdstuberCommented:
rather than converting to text, you can query the date range directly

SELECT *
  FROM yourtable
 WHERE event_date BETWEEN TRUNC(event_date) + 8 / 24 AND TRUNC(event_date) + 10 / 24
   AND event_date > SYSDATE - 7

I'm making the assumption your event_date column is of type DATE
marian68Author Commented:
Yes the table is very big and yes there are I think 3 indexes on this column.

Thank you
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

slightwv (䄆 Netminder) Commented:
How many rows are returned with:  event_date > sydate-7 ?

Try the queries above and see if we need to tweak performance.
sdstuberCommented:
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
marian68Author Commented:
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
slightwv (䄆 Netminder) Commented:
try:
where to_number(to_char(event_date,'HH24')) >= 8 and
to_number(to_char(event_date,'HH24')) < 10 and
event_date > sydate-7;
sdstuberCommented:
>>> 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

sdstuberCommented:
http:#a40395403

still doesn't pick up events that happen at exactly 10:00:00

if you want to do the extracted substring method then try this...

 WHERE TO_CHAR(event_date, 'hh24miss') BETWEEN '080000' AND '100000'
AND event_date > SYSDATE - 7


but again, the direct date comparison does work.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marian68Author Commented:
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,
slightwv (䄆 Netminder) Commented:
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;
sdstuberCommented:
>> >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
slightwv (䄆 Netminder) Commented:
>>>>>> to_number(to_char(event_date,'HH24SS')) < 1001
I meant minute...  typo
to_number(to_char(event_date,'HH24MI')) < 1001

Sorry for the repeat.
sdstuberCommented:
>>> 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
slightwv (䄆 Netminder) Commented:
>>10:00:30  would give a false positive

You are correct.
marian68Author Commented:
Thank you again guys.
sdstuberCommented:
The accepted answer did get a minority share, but you even stated yourself that it doesn't work.  Why was it accepted at all?
marian68Author Commented:
Sorry for the inconvenient.
It is a mistake, I didn't know the solutions would receive a different status.
Thank you again,
marian68Author Commented:
Thank you again
sdstuberCommented:
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.
marian68Author Commented:
As I above mentioned his solution partially worked.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.