I’m new to Grafana and I want to visualize data from Postgres. The table has fields like timestamp, temperature, and sensor_id (uuid). Is it possible to create a separate line for each uuid without having to use multiple queries?
Yes, just GROUP BY that sensor_id column, which will create dedicated timeseries for each sensor_id.
Example (not copy&paste because I don’t have idea about your table structure, so don’t blame if it is doesn’t work, but improve it for your case, pls):
SELECT
$__timeGroupAlias(timecolumn),
sensor_id AS metric,
AVG(temperature) AS value
FROM table
WHERE
$__timeFilter(timecolumn)
GROUP BY 1,2
ORDER BY 1I changed to SQL a bit:
SELECT "timestamp", sensor_id AS metric, AVG(temprature) AS value
FROM temprature
GROUP BY 1, 2
ORDER BY 1
This still returns one line only.
Is “timeGroupAlias” and “timeFilter” important? I got “interpolation failed: macro __timeGroup needs time column and interval and optional fill value” so I removed it.
Debug results:
- use
Table viewPanel editor overview | Grafana documentation - use
Query inspectorHow To Use Grafana's Query Inspector to troubleshoot issues
You are not showing raw data from that query, so only guess it possible to find what can be wrong. And we can spend a days guessing or you can provide reproducible example with exact results/raw data from the query and then it is possible to pin point what’s wrong in the second. Guess: sensor_id is numeric - cast it to be text, because Grafana is picky for data types.
Read doc: PostgreSQL data source | Grafana documentation - there are macros and each is for different time column type (there is not just “one” time type in PostgreSQL, so of course my example may not work if you, because you may have different time type in your table).
I’ll send a screenshot of the Table View, The DB and the Query inspector.
{
"request": {
"url": "api/ds/query?ds_type=grafana-postgresql-datasource&requestId=Q144",
"method": "POST",
"data": {
"queries": [
{
"refId": "A",
"datasource": {
"type": "grafana-postgresql-datasource",
"uid": "adgba1fxczthcd"
},
"rawSql": "SELECT\r\n \"timestamp\" AS \"time\",\r\n temprature AS metric,\r\n sensor_id AS \"series\"\r\nFROM\r\n temprature\r\nORDER BY\r\n \"timestamp\"\r\nLIMIT\r\n 50",
"format": "table",
"datasourceId": 1,
"intervalMs": 60000,
"maxDataPoints": 2609
}
],
"from": "1711003868858",
"to": "1711025468858"
},
"hideFromInspector": false
},
"response": {
"results": {
"A": {
"status": 200,
"frames": [
{
"schema": {
"refId": "A",
"meta": {
"typeVersion": [
0,
0
],
"executedQueryString": "SELECT\r\n \"timestamp\" AS \"time\",\r\n temprature AS metric,\r\n sensor_id AS \"series\"\r\nFROM\r\n temprature\r\nORDER BY\r\n \"timestamp\"\r\nLIMIT\r\n 50"
},
"fields": [
{
"name": "time",
"type": "time",
"typeInfo": {
"frame": "time.Time",
"nullable": true
}
},
{
"name": "metric",
"type": "number",
"typeInfo": {
"frame": "float64",
"nullable": true
}
},
{
"name": "series",
"type": "string",
"typeInfo": {
"frame": "string",
"nullable": true
}
}
]
},
"data": {
"values": [
[
1711016631828,
1711016772342,
1711016773673,
1711016774634,
1711016890219,
1711017628902,
1711017640039,
1711017640246,
1711017640414,
1711017640631,
1711017640795,
1711017782222,
1711017784536,
1711017786969,
1711017789550,
1711017881066,
1711017889558,
1711018372014,
1711018374261,
1711018376541,
1711018378224,
1711018379763,
1711019045417,
1711019107115,
1711021866575,
1711021870368
],
[
35.5,
35.5,
35.5,
35.5,
5.5,
20,
20,
20,
20,
20,
20,
15.5,
20,
23,
17,
40,
40,
35,
32,
30,
25,
20,
50,
20,
10,
15
],
[
"ea7f5256-a8ae-481f-aaa3-d9ba0cef0b72",
"ea7f5256-a8ae-481f-aaa3-d9ba0cef0b72",
"ea7f5256-a8ae-481f-aaa3-d9ba0cef0b72",
"ea7f5256-a8ae-481f-aaa3-d9ba0cef0b72",
"ea7f5256-a8ae-481f-aaa3-d9ba0cef0b72",
"ea7f5256-a8ae-481f-aaa3-d9ba0cef0b72",
"ea7f5256-a8ae-481f-aaa3-d9ba0cef0b72",
"ea7f5256-a8ae-481f-aaa3-d9ba0cef0b72",
"ea7f5256-a8ae-481f-aaa3-d9ba0cef0b72",
"ea7f5256-a8ae-481f-aaa3-d9ba0cef0b72",
"ea7f5256-a8ae-481f-aaa3-d9ba0cef0b72",
"ea7f5256-a8ae-481f-aaa3-d9ba0cef0b72",
"ea7f5256-a8ae-481f-aaa3-d9ba0cef0b72",
"ea7f5256-a8ae-481f-aaa3-d9ba0cef0b72",
"ea7f5256-a8ae-481f-aaa3-d9ba0cef0b72",
"ea7f5256-a8ae-481f-aaa3-d9ba0cef0b72",
"ea7f5256-a8ae-481f-aaa3-d9ba0cef0b72",
"ea7f5256-a8ae-481f-aaa3-d9ba0cef0b72",
"ea7f5256-a8ae-481f-aaa3-d9ba0cef0b72",
"ea7f5256-a8ae-481f-aaa3-d9ba0cef0b72",
"ea7f5256-a8ae-481f-aaa3-d9ba0cef0b72",
"ea7f5256-a8ae-481f-aaa3-d9ba0cef0b72",
"ea7f5256-a8ae-481f-aaa3-d9ba0cef0b72",
"ea7f5256-a8ae-481f-aaa3-d9ba0cef0b72",
"7e1e0513-e45d-4e7b-9be3-e9173994943a",
"7e1e0513-e45d-4e7b-9be3-e9173994943a"
]
],
"nanos": [
[
734000,
573000,
389000,
658000,
951000,
385000,
532000,
77000,
849000,
795000,
167000,
65000,
194000,
395000,
612000,
549000,
303000,
827000,
840000,
7000,
317000,
618000,
238000,
253000,
766000,
490000
],
null,
null
]
}
}
],
"refId": "A"
}
}
}
}
The sensor_id is not numeric and of type uuid. Could this be the problem?
It worked. Thank you very mouch for the help.
1 Like