How to exclude certain hours and certain days in a date range in oracle sql

Oracle development
Oracle SQL
hello, This is the table schema that i have: CREATE TABLE TEST ( COL1 NUMBER NOT NULL, START_TIME DATE NOT NULL, DURATION NUMBER, COL2 NUMBER, CONSTRAINT PK_TEST PRIMARY KEY ( COL1, START_TIME ) This is the query i have to select all the rows within a time range: SELECT f.col2, TRUNC(f.START_TIME,'DD'), FROM test f WHERE (f.START_TIME >= TIMESTAMP '2007-04-13 00:00:00' AND f.START_TIME < TIMESTAMP '2007-07-20 00:00:00' ) GROUP BY TRUNC(f.START_TIME,'DD') If I want to exclude certain days, say Wednesday and Friday, how would the query be?? If I want to exclude certain hours, say the 3rd, 4th, 5th hour of the day and 23rd hour of the day(i.e, excluding 2am - 5am and 10pm - 11pm). Is it possible for me to provide just the list of hours i want to exclude, like [3,4,5,23] or do i need to give the range specifically? Could you please tell me how the queries would be for the above cases. The hours and days to be excluded can vary, so the query shouldnt be specific for the numbers i have used.

Answer Wiki

Thanks. We'll let you know when a new response is added.

Wow, depending on the number of rows, performance could be an issue. Now using your examples from above (I know you said I didn’t have to, but I am not that clever), your queries should look like this:

FROM test f
(f.START_TIME >= TIMESTAMP ‘2007-04-13 00:00:00’ AND f.START_TIME < TIMESTAMP ‘2007-07-20 00:00:00’ )
<b>and start_time between to_timestamp(‘2007-04-13 02:00:00′,’YYYY-MM-DD HH24:MI:SS’)
and to_timestamp(‘2007-04-13 06:00:00′,’YYYY-MM-DD HH24:MI:SS’)
and to_char(start_time,’HH24′) < > ’22’ </b>

Be aware that since we are using a function on the start_time column in the last where critieria, the index on start_time (if there is one) will not be used. You can rewrite the query to look like the between statement prior to the to_char line.

Hope this helps…


To exclude certain days, you could also use the “to_char” function with a single ‘D’ as the format.

For example, to exclude Wednesday and Friday, the condition should look like this:

<pre>and to_char(f.start_time,’D’) not in (4,6)</pre>

Discuss This Question: 1  Reply

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • carlosdl
    To exclude certain hours I would use the same approach as the one I suggested for the days exclusion (i.e. the to_char function). For example: To exclude the hours that Nida mentioned in the question, the condition should look like this:
    and to_char(f.start_time,'HH24') not in (3,4,5,23)
    85,480 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: