Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Filtering between certain hours

Posted on 2014-10-21
22
Medium Priority
?
142 Views
Last Modified: 2014-10-23
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,
0
Comment
Question by:marian68
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
  • 6
22 Comments
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 400 total points
ID: 40395126
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.
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 1600 total points
ID: 40395147
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
0
 

Author Comment

by:marian68
ID: 40395165
Yes the table is very big and yes there are I think 3 indexes on this column.

Thank you
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 77

Expert Comment

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

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

Expert Comment

by:sdstuber
ID: 40395179
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
0
 

Author Comment

by:marian68
ID: 40395392
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
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40395403
try:
where to_number(to_char(event_date,'HH24')) >= 8 and
to_number(to_char(event_date,'HH24')) < 10 and
event_date > sydate-7;
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40395431
>>> 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

0
 
LVL 74

Accepted Solution

by:
sdstuber earned 1600 total points
ID: 40395441
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.
0
 

Author Comment

by:marian68
ID: 40395572
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,
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40395580
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;
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40395606
>> >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
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40395615
>>>>>> to_number(to_char(event_date,'HH24SS')) < 1001
I meant minute...  typo
to_number(to_char(event_date,'HH24MI')) < 1001

Sorry for the repeat.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40395632
>>> 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
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40395644
>>10:00:30  would give a false positive

You are correct.
0
 

Author Comment

by:marian68
ID: 40395838
Thank you again guys.
0
 
LVL 74

Expert Comment

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

Author Comment

by:marian68
ID: 40397521
Sorry for the inconvenient.
It is a mistake, I didn't know the solutions would receive a different status.
Thank you again,
0
 

Author Closing Comment

by:marian68
ID: 40399187
Thank you again
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40399196
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.
0
 

Author Comment

by:marian68
ID: 40399876
As I above mentioned his solution partially worked.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question