How to create a time series stacked area without repeating the query

Grafana Time Series Panel
1

Hello guys, I’m trying to create a time series panel with stacked area, and for that I have 3 querys like this

SELECT STR_TO_DATE(CONCAT(year, '-01-01'), '%Y-%m-%d') AS time, quantity AS 'Total CO₂'
FROM mydb.my_table
WHERE category = 'TG' AND party = '$party' AND gas_type = 'CO₂'
ORDER BY time;

SELECT STR_TO_DATE(CONCAT(year, '-01-01'), '%Y-%m-%d') AS time, quantity AS 'Total N₂O'
FROM mydb.my_table
WHERE category = 'TG' AND party = '$party' AND gas_type = 'N₂O'
ORDER BY time;

SELECT STR_TO_DATE(CONCAT(year, '-01-01'), '%Y-%m-%d') AS time, quantity AS 'Total CH₄'
FROM mydb.my_table
WHERE category = 'TG' AND party = '$party' AND gas_type = 'CH₄'
ORDER BY time;

And with this I get this chart

image
image844×290 34.4 KB

My current problem is that the user can add more gases, and then he needs to add a new more query. Can I do something that creates me a stacked area panel with all gases present on the table with the exception to exclude when the gas_type is null? Or do I need to add a new query every time a new gas it’s added.

If the user adds a new gas, can I update the panel JSON via API to insert a new query?

2

Might be easier to do

gas_type in (${gases:singlequote})

$gases being from a variable of all gasses displayed to user or hidden that excludes null values

Or you can get rid of the where clause of gas_type= ‘co2’

3

You need GROUP BY, e.g.:

SELECT 
  STR_TO_DATE(CONCAT(year, '-01-01'), '%Y-%m-%d') AS time, 
  quantity AS 'value',
  gas_type AS 'metric',
FROM mydb.my_table
WHERE 
  category = 'TG' AND 
  party = '$party'
GROUP BY 1, 2
ORDER BY 1;

It may not be exact copy&paste query, so tweak any syntax issues or some SQL flavor specialities.

4

I tried this

SELECT 
  STR_TO_DATE(CONCAT(year, '-01-01'), '%Y-%m-%d') AS time, 
  quantity AS 'value',
  gas_type AS 'metric'
FROM mydb.my_table
WHERE 
  category = 'TG' AND 
  party = 'pt' AND
  gas_type IS NOT NULL
GROUP BY 1, 2, 3
ORDER BY 1;

And got this

image
image844×288 32.3 KB

5

I don’t want to pass a variable, can I get the gases from the table? How the query will look like, can you please show me.

I’m, new to Grafana, and I’m trying to learn.

I tried something like

SELECT STR_TO_DATE(CONCAT(year, '-01-01'), '%Y-%m-%d') AS time, quantity AS 'Total CO₂'
FROM mydb.my_table
WHERE category = 'TG' AND party = '$party' AND gas_type in ('CO₂','N₂O')
ORDER BY time;
6 
SELECT STR_TO_DATE(CONCAT(year, '-01-01'), '%Y-%m-%d') AS time, quantity AS 'Total CO₂'
FROM mydb.my_table
WHERE category = 'TG' AND party = '$party' AND gas_type in (select distinct gas_type from 
FROM mydb.my_table where gas_type is not null
)
ORDER BY time;

But this is really not a grafana issue.
Also want to point out your schema design is not well normalized. Why would there be cases where gas type is null?

7

I will test it later, but just to answer tour queation I have Null values because the table have another columns like category etc and some categories can have a gas_type or not

1 Like
8

Yeah, you might want to consider breaking that apart with a gas_types table. Tables are cheap. But that’s a totally different subject outside of grafana

9

Try:

SELECT 
  STR_TO_DATE(CONCAT(year, '-01-01'), '%Y-%m-%d') AS time, 
  AVG(quantity) AS 'value',
  gas_type AS 'metric'
FROM mydb.my_table
WHERE 
  category = 'TG' AND 
  party = 'pt' AND
  gas_type IS NOT NULL
GROUP BY 1, 2, 3
ORDER BY 1;
1 Like
10

I tried this and didn’t work too, I don’t know if it because of my table, but your query returns me this values

1990-01-01;3.4580589695131
1990-01-01;10.7364749169210
1990-01-01;45.2788208529410
1991-01-01;10.8727866313730
1991-01-01;47.0970857086570
1991-01-01;3.4276155696097
... goes one until 2021, but always 3 by 3, first CO2, then N2O then CH4

And the chart that I got with one query only is

image
image724×249 23.2 KB

And I want to show it like in the first pic.