Grouping By Day / Stacked Chart

[ssnkhan]

I am trying to create a stacked chart, with each bar representing 1 day. My original sqlite query is:

SELECT
    strftime("%Y-%m-%d", artifacts.created_at) AS "Date",
    alerts.title AS Type,
    count(*) AS Total
FROM artifacts
INNER JOIN alerts ON alerts.id = artifacts.alert_id
GROUP BY
    Date, Type
ORDER BY
    Date DESC

In Grafana, I have created a TimeSeries query as follows:

SELECT
    REPLACE (artifacts.created_at, ' ', 'T') || 'Z' AS Date,
    alerts.title AS Type,
    count(*) AS ""
FROM artifacts
INNER JOIN alerts ON alerts.id = artifacts.alert_id
GROUP BY
   Date, Type
order by Date ASC

This creates the following chart:

Seems that Grafana is drawing each element horizontally, rather than grouping by date and placing each date's value in a single bar. I suspect this is partially due to to each row not being clustered into a 24 hour period (I have set the Interval to 24h in the Query Editor).

I have seen other queries make use of $__timeGroup, setting this to 24h, but this has not worked and I seem to be at an impasse. Not sure if there is something obvious I am missing, but any help would be appreciated.

[fr-ser]

I see no problem on my end. Did you choose the right Grafana visualization settings?

[ssnkhan]

Hmm, this is so odd. I can't even get a single Type to render correctly (per day). The Inspector:Data view also just shows a single date:value row, rather than one per day.

As a "normal" SQL query, the results I get are like:

Date, Type (Count)
2020-01-01, 4
2020-01-02, 5
2020-01-03, 6
...

But in the Grafana query, I get a single row returned, where the count is the sum of all rows, and where the Date is the earliest date in the table:

Date, Type (Count)
2020-01-01, 15

If I can get the bar chart working for a single Type, it should be easy to get the rest worked in.

[fr-ser]

The Inspector:Data view also just shows a single date:value row

In my experience, the data inspector does not work well with time series returned as multiple data frames.

But as you can see from the screenshot above it should work. Now it is just a question of what you are doing differently. E.g. I am using a time column (not a date-string) and I have enabled the stack option

[ssnkhan]

E.g. I am using a time column (not a date-string) and I have enabled the stack option

I have tried using strftime to convert to unix epoch:

SELECT
    strftime("%s", artifacts.created_at) AS Date,
    alerts.title AS Type,
    count(*) AS ""
FROM artifacts
INNER JOIN alerts ON alerts.id = artifacts.alert_id
group by Date
order by Date ASC

When I set the graph type to table, I can see that the data is correctly being returned. However, the date column shows the date as datetime, like 2021-03-05 10:34:02, rather than grouping by day, like 2021-03-05. I am not sure how Grafana nows to group per day -- is this what you are doing with the "Aggregation Interval"? I cannot see that in my instance at all.

[fr-ser]

If you want to group by day, then you should convert the timestamp to a day and constant time (e.g. only take the "date" part of the time and append "T12:00Z" to it). Only then you can reasonably group by it

[ssnkhan]

That worked perfectly, thank you!