Oracle formatting date intervals

Question

I am trying to create a query to generate statistical data for my website, and I have trying to figure out how to craft a query for half hour intervals.

Based on another question I stumbled across, I have come up with this :

SELECT count(*), dt FROM (
  (SELECT TO_CHAR(TRACK_DATETIME,'dd/mm/yyyy hh24') || ':' || DECODE(TRUNC(to_number(to_char(TRACK_DATETIME,'mi'))/30),0,'00','30') as DT FROM tbl_stat)
)
group by dt

This works great, with the only exception being that intervals with no activity do not show up at all. Is there a way to show all half hour intervals (even empty ones?)

Thanks

possible duplicate of [Grouping records hour by hour or day by day and filling gaps with zero or null](http://stackoverflow.com/questions/10798905/grouping-records-hour-by-hour-or-day-by-day-and-filling-gaps-with-zero-or-null) — Alex Poole, Jul 11 '12 at 19:34

score 0 · Answer 1 · answered Jul 12 '12 at 12:23

The trick here is to generate a list of all the intervals, and then do an outer join to the table to see how many records you have in each interval.

Assuming you have a range for the query (which here I'm setting with varchar2 bind variables just so I can run this easily in SQL*Plus, you'd do something else from your web client, e.g. ? for JDBC) you can do something like:

var start_time varchar2(16);
var end_time varchar2(16);

exec :start_time := '12/07/2012 08:00:00';
exec :end_time := '12/07/2012 12:00:00';

select start_time + (level - 1)/48 as period_start,
    start_time + level/48 - interval '1' second as period_end
from (
    select to_date(:start_time, 'DD/MM/YYYY HH24:MI:SS') start_time,
        to_date(:end_time, 'DD/MM/YYYY HH24:MI:SS') end_time
    from dual
)
connect by start_time + (level - 1)/48 < end_time;

Which generates a list of all the half-hour periods between 08:00 and 12:00:

PERIOD_START        PERIOD_END
------------------- -------------------
12/07/2012 08:00:00 12/07/2012 08:29:59
12/07/2012 08:30:00 12/07/2012 08:59:59
12/07/2012 09:00:00 12/07/2012 09:29:59
12/07/2012 09:30:00 12/07/2012 09:59:59
12/07/2012 10:00:00 12/07/2012 10:29:59
12/07/2012 10:30:00 12/07/2012 10:59:59
12/07/2012 11:00:00 12/07/2012 11:29:59
12/07/2012 11:30:00 12/07/2012 11:59:59

Then you can use that as a CTE or subquery and find matching records in your real table:

with tmp_tab as (
    select start_time + (level - 1)/48 as period_start,
        start_time + level/48 - interval '1' second as period_end
    from (
        select to_date(:start_time, 'DD/MM/YYYY HH24:MI:SS') start_time,
            to_date(:end_time, 'DD/MM/YYYY HH24:MI:SS') end_time
        from dual
    )
    connect by start_time + (level - 1)/48 < end_time
)
select to_char(tt.period_start, 'DD/MM/YYYY HH24:MI') dt,
    count(ts.track_datetime)
from tmp_tab tt
left join tbl_stat ts
on ts.track_datetime between tt.period_start and tt.period_end
group by tt.period_start
order by tt.period_start;

With a dummy table with only a few records in, this gives:

DT               COUNT(TS.TRACK_DATETIME)
---------------- ------------------------
12/07/2012 08:00                        0
12/07/2012 08:30                        0
12/07/2012 09:00                        1
12/07/2012 09:30                        2
12/07/2012 10:00                        0
12/07/2012 10:30                        1
12/07/2012 11:00                        0
12/07/2012 11:30                        0

Oracle formatting date intervals

1 Answers1