Multiple template variables postgresql

Grafana
1

I have the following postgresql database with a TABLE called temperature with the columns,

time,
location,
value
The location is an integer which varies from 1 to 10.

Using a template variable for location, I’m able to define a variable called location and have set it to multiple values with select all. How do I modify the sql query to plot the temperature from all selected locations on one graph. Or how does one format the multiple values into an sql query?

Thanks

Edit to add: solved. Use

SELECT
  "time" AS "time",
  value,
  location::text as metric
FROM temperature
where
location in (${location:csv})
ORDER BY 1
2

If I understand, you want to group by location? In that case, just add a group by:

SELECT
  "time" AS "time",
  value,
  location::text as metric
FROM temperature
where
location in (${location:csv})
GROUP BY time, metric
ORDER BY 1
3

@daniellee – thanks for responding.

The group by did not work. I got an sql error. I will try to investigate more. If I remove the Group By it does what I need.

SELECT
  "time" AS "time",
  value,
  location::text as metric
FROM temperature
where
location in (${location:csv})
ORDER BY 1
4

Sounds like I didn’t understand your question - what is the problem you are having? Can you explain it in more detail?

5

@daniellee – Thanks for your response. My problem is solved and I should have marked it as such.

Let me also start by saying that I’m a complete newbie trying to come up to speed with both Grafana and Postgresql but have lots of experience programming in general. So most of my issues are probably related to my newbieness.

And you are right, I did not explain my problem clearly as I left out a key fact that I was trying to use a templated variable for the location. So here is the problem I was trying to solve. The data is in a postgresql table with timescaledb extension installed.

Table is called temperature
There are 3 columns.
Column 1 is labelled time and is timestamp with timezone
Column 2 is labelled location and is an integer which represents various locations.
Column 3 is labelled value and is the temperature reading at that location and time.

I first created a variable called location in my dashboard. It takes on values from 1 to 8 corresponding to my various locations. I set it to be multi valued and with option to select all.

My first challenge was how to access the variable I had created with the quotes etc escaped properly. The templating document on grafana does explain all of this and it was a question of trying out the various ways. The (${location:csv}) worked for me.

The next issue was to be able to be able to plot the various locations in various colors. For that I got it to work by the location::text as metric by the tip given in this thread.

1 Like