Oracle - Generate a list of dates with time

I had to create a report that had to have a line for each date/time within the given range, even if the actual data did not have value for that date. Say, web trafic count for each 5 minutes for a given day. The simple full join of the date will not work for this because, there might be periods during the day there was no user activity, and it is expected to have zeroes in that case.

A simple approach would be to use the hirerical query used in this blog if you only need one row per date. But if you need date entries for multiple times per day, say a row for every 5 minutes, the following function will come in handy. It uses a Pipelined Table Function.

CREATE OR REPLACE TYPE date_array AS TABLE OF DATE;
/
CREATE OR REPLACE FUNCTION MPI2.date_table(sdate DATE, edate DATE, mi_interval integer   )

RETURN date_array PIPELINED is
 fDate date ;
BEGIN
  fDate := sdate;
  while fDate < edate LOOP
    PIPE ROW(fDate );
    fDate := fDate +  mi_interval / 1440;  
  END LOOP;
  RETURN;
END date_table;

Now you may query this function just like you query a table, as follows

SELECT to_char(column_value,'DD-MON-YYYY HH24:MI')  FROM TABLE(CAST(MPI2.DATE_TABLE(trunc(sysdate-1),sysdate,5) AS date_array));

The above query will return a row for every 5 minutes. You can do a full join of this table with your actual tables to get the result you wanted.