Generate time series for charts in DB2 for i 📈

When you want to fetch data to display a chart in a user interface, with data grouped per hour or day, it can be difficult to get rows that contain 0. They are omitted when using group by, leaving your time series full of holes. This will lead to the user interface not being able to display your chart nicely. You want hours/days with zeroes for your chart to make sense.

For example, if you are grouping per hour for the last 24h, only the hours that contain rows in the group will be fetched. If only 3 hours contain rows, it will lead to a result set like this:

[5, 2, 3]

And you want a result like this:

[0, 0, 0, 0, 0, 0, 0, 0, 0, 5, 0, 0, 2, 0, 3, 0, 0, 0, 0, 0, 0, 0, 0, 0]

In Postgres, you have the handy function generate_series() available. But you don’t have that in DB2. So what are your options?

In order to get the number of orders per hour for the last 24h, I’ve come up with this query. Using a recursive common table expression (CTE), it creates a result set containing all the last 24 hours, then it fetches the number of orders for each hour through a sub select.

WITH time_series(id) AS (
    SELECT 0 FROM SYSIBM.SYSDUMMY1    
    UNION ALL
    SELECT id + 1 FROM time_series WHERE id < 23
)
SELECT
    ID,
    (
        SELECT
            COUNT(DISTINCT(A.ORDER_ID))
        FROM ORDERS A
        WHERE HOUR(A.CREATED_TIMESTAMP) = HOUR(CURRENT TIMESTAMP - ID HOURS) 
          AND A.CREATED_TIMESTAMP >= CURRENT TIMESTAMP - 24 HOURS
          AND DATE(A.CREATED_TIMESTAMP) >= DATE(CURRENT TIMESTAMP - 24 HOURS)
    )
FROM time_series
ORDER BY ID DESC;

What about last 7 or 14 days? 🤔

The query can easily be changed to deal with days. This example fetches the number of orders per day for the last 14 days:

WITH time_series(id) AS (
    SELECT 0 FROM SYSIBM.SYSDUMMY1    
    UNION ALL
    SELECT id + 1 FROM time_series WHERE id < 13
)
SELECT
    ID,
    (
        SELECT
            COUNT(DISTINCT(A.ORDER_ID))
        FROM ORDERS A
        WHERE DAY(A.CREATED_TIMESTAMP) = DAT(CURRENT TIMESTAMP - ID DAYS) 
          AND A.CREATED_TIMESTAMP >= CURRENT TIMESTAMP - 13 DAYS
          AND DATE(A.CREATED_TIMESTAMP) >= DATE(CURRENT TIMESTAMP - 13 DAYS)
    )
FROM time_series
ORDER BY ID DESC;

I hope you find this post helpful! Let me know if you know a better way to accomplish this!