The answers of Justin and Sebas can be extended by the LEFT JOIN to eliminate “spaces”, which is often desirable.
If this is not necessary, as an alternative, we can go to the old-school Oracle DATE arithmetic ...
SELECT TRUNC(t.time)+FLOOR(TO_CHAR(t.time,'sssss')/300)*300/86400 AS time , AVG(t.value) AS avg_value FROM foo t WHERE t.time IS NOT NULL GROUP BY TRUNC(t.time)+FLOOR(TO_CHAR(t.time,'sssss')/300)*300/86400 ORDER BY TRUNC(t.time)+FLOOR(TO_CHAR(t.time,'sssss')/300)*300/86400
Let me unzip it a bit. We can separate the date and time components using TRUNC to get a portion of the date, and use TO_CHAR to return the number of seconds since midnight. We know that 5 minutes is 300 seconds, and we know that there are 86,400 seconds per day. Thus, we can divide the number of seconds by 300 and take FLOOR of this (only the integer part), which rounds us to the nearest border of 5 minutes. We multiply this back (by 300) to get the seconds again, and then divide it by the number of seconds per day (86400), and we can add this back to the portion of the (shortened) date.
Painful, yes. But incredibly fast.
NOTE: this returns the value of the rounded time as DATE , it can be dropped to the timestamp if necessary, but for even borders for 5 minutes a DATE has sufficient resolution.
As an advantage of this approach for a large table, we can improve query performance by adding a coverage index for this query:
CREATE INDEX foo_FBX1 ON foo (TRUNC(t.time)+FLOOR(TO_CHAR(t.time,'sssss')/300)*300/86400,value);
ADD:
MiMo provided an answer for SQL Server, suggesting that it will be adapted for Oracle. Here is an adaptation of this approach in Oracle. Note that Oracle does not provide equivalents for the DATEDIFF and DATEADD functions. Instead, Oracle uses simple arithmetic.
SELECT TO_DATE('00010101','YYYYMMDD')+FLOOR((t.time-TO_DATE('00010101','YYYYMMDD'))*288)/288 AS time , AVG(t.value) AS avg_value FROM foo t WHERE t.time IS NOT NULL GROUP BY TO_DATE('00010101','YYYYMMDD')+FLOOR((t.time-TO_DATE('00010101','YYYYMMDD'))*288)/288 ORDER BY TO_DATE('00010101','YYYYMMDD')+FLOOR((t.time-TO_DATE('00010101','YYYYMMDD'))*288)/288
Choice January 1, 0001 A.D. as a base date is arbitrary, but I did not want to mess with negative values and find out if FLOOR would be correct, or we would need to use CEIL with negative numbers, (Magic number 288 - result of 1440 minutes per day, divided by 5). In this case, we take a fractional day, multiplying by 1440 and dividing by 5, and take the whole part of it, and then return it on fractional days.
It is tempting to pull this "base date" out of the PL / SQL package or retrieve it from a subquery, but any of them can prevent this expression from being deterministic. And we really would like to open up the possibility of creating a function-based index.
My preference is to avoid having to include a “base date” in the calculation.