Multiple dates within a certain range of time in Oracle SQL

chickennomnoms 0
Viewed 34

I need to pull data for the same timeframe but with different dates. In the simplest form, what I have been doing is

SELECT * FROM table
WHERE organization='ABC' AND
starttime between '09-15-2022 08:00:00' and '09-15-2022 17:00:00'

I would like to pull all the date for 9/12, 9/13, 9/14 as well. Is there anyway I can do that in one script? I have just been copy pasting my code and changing the dates but it gets very tedious and (somewhat) time-consuming since I have to export the report and condense all of the dates into one spreadsheet.

date oracle sql
2 Answers

You could do something like:

SELECT * FROM table
WHERE organization='ABC' AND
starttime >= date '2022-09-12' AND
starttime < date '2022-09-16' AND
extract(hour from cast(starttime as timestamp)) between 8 and 16

That will look for rows at or after midnight on the 12th, and before midnight on the 16th - so covering all data on 12th, 13th, 14th and 15th; and then filter those to time at or after 08:00:00 and before 17:00:00, by looking at just the hour value. (Your current query includes any data at exactly 17:00:00, which you probably don't want.)

You could be a bit more specific in the initial filter:

starttime >= cast(timestamp '2022-09-12 08:00:00' as date) AND
starttime < cast(timestamp '2022-09-15 17:00:00' as date) AND
extract ...

but using date literals (which are at midnight) is a little simpler and there may not be a significant performance difference.

I've assumed the column is data type DATE, hence timestamp literals to dates (though Oracle would do that implicitly anyway), and casting the column to timestamp to allow the hour element to be extracted. If they are actually timestamps then it's a little bit simpler:

SELECT * FROM table
WHERE organization='ABC' AND
starttime >= timestamp '2022-09-12 08:00:00' AND
starttime < timestamp '2022-09-15 17:00:00' AND
extract(hour from starttime) between 8 and 16
Alex Poole0

You can use OR in the WHERE clause to specify multiple ranges:

SELECT *
FROM   table
WHERE  organization='ABC'
AND    (  starttime BETWEEN TIMESTAMP '2022-09-15 08:00:00'
                        AND TIMESTAMP '2022-09-15 17:00:00'
       or starttime BETWEEN TIMESTAMP '2022-09-14 08:00:00'
                        AND TIMESTAMP '2022-09-14 17:00:00'
       or starttime BETWEEN TIMESTAMP '2022-09-13 08:00:00'
                        AND TIMESTAMP '2022-09-13 17:00:00'
       or starttime BETWEEN TIMESTAMP '2022-09-12 08:00:00'
                        AND TIMESTAMP '2022-09-12 17:00:00'
       )

or you can compare on the date and time components separately:

SELECT *
FROM   table
WHERE  organization='ABC'
AND    starttime >= DATE '2022-09-12'
AND    starttime <  DATE '2022-09-16'
AND    (starttime - TRUNC(starttime)) DAY TO SECOND
         BETWEEN INTERVAL '0 08:00:00' DAY TO SECOND
         AND     INTERVAL '0 17:00:00' DAY TO SECOND;

fiddle

MT00
Related