How can I change day's time interval on oracle query?-CodePudding

I have a this query and working correctly.

select TO_CHAR(A.CREATE_DATE, 'DD') DAYs,TO_CHAR(A.CREATE_DATE, 'MM') MONTHS,
SUM(CASE WHEN TO_CHAR(A.RCPT_DTIME,'HH24') = 8 THEN 1 ELSE 0 END) AS TIME8,
SUM(CASE WHEN TO_CHAR(A.RCPT_DTIME,'HH24') = 7 THEN 1 ELSE 0 END) AS TIME7
                              
                              from PERSON A, HRM B
                              where  A.PERSON_ID = B.PERSON_ID                            
                              AND A.CREATE_DATE >= TO_DATE(:i_CREATE_DATE || '00','YYYYMMDDHH24MISS')
                              AND A.CREATE_DATE < TO_DATE(:i_CREATE_DATE || '00','YYYYMMDDHH24MISS')                                 
                              GROUP BY TO_CHAR(A.CREATE_DATE, 'DD'),TO_CHAR(A.RCPT_DTIME, 'MM')
                              ORDER BY MONTHS,DAYs

Normal query's time get start from 00:00 to day after day's 00:00. But I want to make day's time range as day's 07:00am and tomorrow's 07:00am. How can I make this one?

CodePudding user response：

You can TRUNC the date and then add 7 hours. As example this will select today 7am and tomorrow 7am:

SELECT TRUNC(sysdate)   INTERVAL '7' HOUR, 
TRUNC(sysdate)   1   INTERVAL '7' HOUR FROM dual;

So you can use this within a where clause, something like

SELECT columnselection FROM tableselection 
WHERE ...AND TRUNC(yourdate)   INTERVAL '7' HOUR <= datetocompare 
AND TRUNC(yourdate)   1   INTERVAL '7' HOUR >= datetocompare AND...

You could also use BETWEEN when comparing dates instead of <= and >=, some people think this is easier to read.