Question

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

Oracle SQL, Oracle development

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.

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:

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' )
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'
GROUP BY
TRUNC(f.START_TIME,'DD')

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...

-Dave

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:

and to_char(f.start_time,'D') not in (4,6)

-cadlg