Time buckets for charts

W

When I need to show data as chart with data grouped to time buckets (interval between 1 to 60 minutes) I use this script. So in the example below I select number of rows in logs table (record_) grouped to 15 minutes intervals (bucket_id). With joining x table I achieve zero blanks.

You can find more about WITH functions on this excellent blog by Tim Hall.

WITH
    FUNCTION get_time_bucket (
        in_date             DATE,
        in_interval         NUMBER
    )
    RETURN NUMBER AS
    BEGIN
        RETURN FLOOR((in_date - TRUNC(in_date)) * 1440 / in_interval) + 1;
    END;
    --
x AS (
    SELECT
        LEVEL AS bucket_id
        --CAST(TRUNC(SYSDATE) + NUMTODSINTERVAL((LEVEL - 1) * 15, 'MINUTE') AS DATE) AS start_at,
        --CAST(TRUNC(SYSDATE) + NUMTODSINTERVAL( LEVEL      * 15, 'MINUTE') AS DATE) AS end_at
    FROM DUAL
    CONNECT BY LEVEL <= (1440 / 15)
),
l AS (
    SELECT
        get_time_bucket(l.created_at, 15)   AS bucket_id,
        COUNT(*)                            AS records_
    FROM logs l
    --WHERE l.created_at >= TRUNC(SYSDATE)
    GROUP BY get_time_bucket(l.created_at, 15)
)
SELECT
    x.bucket_id,
    l.records_
FROM x
LEFT JOIN l
    ON l.bucket_id = x.bucket_id
ORDER BY 1;
/