SELECT
MIN(TRUNC("timeStamp", 'HH') + NUMTODSINTERVAL(FLOOR(EXTRACT(MINUTE FROM "timeStamp") / 5) * 5, 'MINUTE')) AS min_rounded_timestamp,
MAX(TRUNC("timeStamp", 'HH') + NUMTODSINTERVAL(CEIL(EXTRACT(MINUTE FROM "timeStamp") / 5) * 5, 'MINUTE')) AS max_rounded_timestamp
FROM
your_table_name;
WITH intervals AS (
SELECT
TO_DATE('2023-09-12 08:00:00', 'YYYY-MM-DD HH24:MI:SS') + NUMTODSINTERVAL((LEVEL - 1) * 5, 'MINUTE') AS interval_start,
TO_DATE('2023-09-12 08:00:00', 'YYYY-MM-DD HH24:MI:SS') + NUMTODSINTERVAL(LEVEL * 5, 'MINUTE') AS interval_end
FROM
DUAL
CONNECT BY
LEVEL <= (TO_DATE('2023-09-12 09:00:00', 'YYYY-MM-DD HH24:MI:SS') - TO_DATE('2023-09-12 08:00:00', 'YYYY-MM-DD HH24:MI:SS')) * 1440 / 5
)
SELECT
i.interval_start,
i.interval_end,
y.*
FROM
your_table_name y
JOIN
intervals i
ON
y.updated_ts >= i.interval_start AND y.updated_ts < i.interval_end;