TIMESTAMP rollup functions for Oracle

example.sql

SELECT g.ts, c##mon.rollup_timestamp(g.ts, numtodsinterval(2, 'minute')) rollup_2min, c##mon.rollup_timestamp_tz(g.tstz, numtodsinterval(5, 'minute')) rollup_5min
FROM (SELECT sys_extract_utc(systimestamp+numtodsinterval(rownum, 'minute')) ts, systimestamp+numtodsinterval(rownum, 'minute') tstz
      FROM all_objects FETCH FIRST 20 ROWS ONLY) g;
      
TS                            ROLLUP_2MIN                   ROLLUP_5MIN                      
----------------------------- ----------------------------- ---------------------------------
2021-08-31 15:26:48.918821000 2021-08-31 15:26:00.000000000 2021-08-31 15:25:00.000000000 UTC
2021-08-31 15:27:48.918821000 2021-08-31 15:28:00.000000000 2021-08-31 15:30:00.000000000 UTC
2021-08-31 15:28:48.918821000 2021-08-31 15:28:00.000000000 2021-08-31 15:30:00.000000000 UTC
2021-08-31 15:29:48.918821000 2021-08-31 15:30:00.000000000 2021-08-31 15:30:00.000000000 UTC
2021-08-31 15:30:48.918821000 2021-08-31 15:30:00.000000000 2021-08-31 15:30:00.000000000 UTC
2021-08-31 15:31:48.918821000 2021-08-31 15:32:00.000000000 2021-08-31 15:30:00.000000000 UTC
2021-08-31 15:32:48.918821000 2021-08-31 15:32:00.000000000 2021-08-31 15:35:00.000000000 UTC
2021-08-31 15:33:48.918821000 2021-08-31 15:34:00.000000000 2021-08-31 15:35:00.000000000 UTC
2021-08-31 15:34:48.918821000 2021-08-31 15:34:00.000000000 2021-08-31 15:35:00.000000000 UTC
2021-08-31 15:35:48.918821000 2021-08-31 15:36:00.000000000 2021-08-31 15:35:00.000000000 UTC
2021-08-31 15:36:48.918821000 2021-08-31 15:36:00.000000000 2021-08-31 15:35:00.000000000 UTC
2021-08-31 15:37:48.918821000 2021-08-31 15:38:00.000000000 2021-08-31 15:40:00.000000000 UTC
2021-08-31 15:38:48.918821000 2021-08-31 15:38:00.000000000 2021-08-31 15:40:00.000000000 UTC
2021-08-31 15:39:48.918821000 2021-08-31 15:40:00.000000000 2021-08-31 15:40:00.000000000 UTC
2021-08-31 15:40:48.918821000 2021-08-31 15:40:00.000000000 2021-08-31 15:40:00.000000000 UTC
2021-08-31 15:41:48.918821000 2021-08-31 15:42:00.000000000 2021-08-31 15:40:00.000000000 UTC
2021-08-31 15:42:48.918821000 2021-08-31 15:42:00.000000000 2021-08-31 15:45:00.000000000 UTC
2021-08-31 15:43:48.918821000 2021-08-31 15:44:00.000000000 2021-08-31 15:45:00.000000000 UTC
2021-08-31 15:44:48.918821000 2021-08-31 15:44:00.000000000 2021-08-31 15:45:00.000000000 UTC
2021-08-31 15:45:48.918821000 2021-08-31 15:46:00.000000000 2021-08-31 15:45:00.000000000 UTC

20 rows selected. 

timestamp_rollup.sql

create or replace function interval_to_seconds(p_int interval day to second)
    RETURN number
    DETERMINISTIC
    PARALLEL_ENABLE
IS
    PRAGMA UDF;
begin
    -- Converts interval to seconds
    -- 2020 Ilmar Kerm
    RETURN extract(day from p_int)*86400+extract(hour from p_int)*3600+extract(minute from p_int)*60+extract(second from p_int);
end;
/

create or replace function rollup_timestamp(p_timestamp IN TIMESTAMP, p_interval IN INTERVAL DAY TO SECOND DEFAULT numtodsinterval(5, 'minute'))
    RETURN timestamp
    DETERMINISTIC
    PARALLEL_ENABLE
IS
    PRAGMA UDF;
    v_epoch TIMESTAMP;
    v_interval NUMBER;
    v_new_interval NUMBER;
BEGIN
    -- Rounds timestamp to nearest interval.
    -- Used to emulate GROUP BY time(<time interval>) in InfluxDB
    -- 2021 Ilmar Kerm
    v_interval:= round(interval_to_seconds(p_interval));
    
    IF p_interval <= numtodsinterval(1, 'day') THEN
        -- Calculation is based on midnight on the same day
        v_epoch:= TRUNC(p_timestamp, 'DD');
    ELSE
        -- Shouldn't really be here, but just in case
        -- This is to avoid ORA-01873: the leading precision of the interval is too small
        -- And it should rarely be needed
        raise_application_error(-20934, 'Keep the interval to less than a day.');
    END IF;
    
    -- Should it be ROUND (nearest) or FLOOR (always down)?
    v_new_interval:= round(interval_to_seconds(p_timestamp - v_epoch) / v_interval) * v_interval;
    
    RETURN v_epoch + numtodsinterval(v_new_interval, 'second');
END;
/

create or replace function rollup_timestamp_tz(p_timestamp IN TIMESTAMP WITH TIME ZONE, p_interval IN INTERVAL DAY TO SECOND DEFAULT numtodsinterval(5, 'minute'))
    RETURN timestamp with time zone 
    DETERMINISTIC
    PARALLEL_ENABLE
IS
    PRAGMA UDF;
BEGIN
    -- Rounds timestamp to nearest interval.
    -- Used to emulate GROUP BY time(<time interval>) in InfluxDB
    -- 2021 Ilmar Kerm
    
    RETURN from_tz(rollup_timestamp(sys_extract_utc(p_timestamp), p_interval), 'UTC');
END;
/