How Can We Get The 15 Minutes Time Interval

I am trying to fetch every 15min data in such a way that, if the current time is 23-10-19 11:11:30 then I need to get the data from 23-10-19 10:30:59 to 23-10-19 10:45:59 in the sa

Solution 1:

You can truncate to the nearest minute to zero the seconds and then subtract the number of minutes to get back to the nearest 15 minute interval past the hour and then apply your offsets:

SELECT TRUNC( current_timestamp, 'MI' )
         -MOD( EXTRACT( MINUTEFROMcurrent_timestamp ), 15 ) *INTERVAL'1'MINUTE-INTERVAL'30'MINUTE+INTERVAL'59'SECONDAS start_time,
       TRUNC( current_timestamp, 'MI' )
         -MOD( EXTRACT( MINUTEFROMcurrent_timestamp ), 15 ) *INTERVAL'1'MINUTE-INTERVAL'15'MINUTE+INTERVAL'59'SECONDAS end_time,
       current_timestampFROM   DUAL

Outputs:

START_TIME          | END_TIME            | CURRENT_TIMESTAMP            
:------------------ | :------------------ | :----------------------------
2019-10-23 09:00:59 | 2019-10-23 09:15:59 | 2019-10-23 09:42:53.742684000

db<>fiddle here

Solution 2:

Another solution is this one:

WITH t AS 
    (SELECT TRUNC(CURRENT_TIMESTAMP , 'hh') + TRUNC(EXTRACT(MINUTEFROMCURRENT_TIMESTAMP ) /15) *INTERVAL'15'MINUTE-INTERVAL'30'MINUTEAS Base
    FROM dual)
SELECT Base +INTERVAL'59'SECONDAS begin_time,
    Base +INTERVAL'15:59'minutetoSECONDAS end_time
FROM t;

It is based on my generic Interval function:

CREATEOR REPLACE FUNCTION MakeInterval(ts INTIMESTAMP, roundInterval ININTERVALDAYTOSECOND) RETURNTIMESTAMPDETERMINISTICIS
    denom INTEGER;
BEGIN
    IF roundInterval >=INTERVAL'1'HOURTHEN
        denom :=EXTRACT(HOURFROM roundInterval);
        IF MOD(24, denom) <>0THEN
            RAISE VALUE_ERROR;
        END IF;
        RETURN TRUNC(ts) + TRUNC(EXTRACT(HOURFROM ts) / denom) * denom *INTERVAL'1'HOUR;
    ELSIF roundInterval >=INTERVAL'1'MINUTETHEN
        denom :=EXTRACT(MINUTEFROM roundInterval);
        IF MOD(60, denom) <>0THEN
            RAISE VALUE_ERROR;
        END IF;
        RETURN TRUNC(ts, 'hh') + TRUNC(EXTRACT(MINUTEFROM ts) / denom) * denom *INTERVAL'1'MINUTE;
    ELSE
        denom :=EXTRACT(SECONDFROM roundInterval);                
        IF MOD(60, denom) <>0THEN
            RAISE VALUE_ERROR;
        END IF;
        RETURN TRUNC(ts, 'mi') + TRUNC(EXTRACT(SECONDFROM ts) / denom) * denom *INTERVAL'1'SECOND;
    END IF;
END MakeInterval;

You would invoke it as

SELECT MakeInterval(CURRENT_TIMESTAMP, INTERVAL'15'MINUTE) from dual;

plus/minus your constant offsets.

Solution 3:

select :start_date + (1/96)*(level-1) from dual connect by level < :intervals