I have a table in Mysql with a field that stores the timestamp, another field with the “device_id” and another field with the value “angle”.
In turn, with a variable, I can select the “device_id” from which I want to display data.
But if I select two or more “device_id” it does not show me a line for each one of them but a line continues to appear.
How can I make a line appear showing the “angle” data of each of the “device_id” that I select?
Thanks
Try to select also device_id in the SELECT section.
Yes, that’s only one part. Use query inspector to see how is that variable populated with multiple values selected and set correct variable format, so right SQL syntax will be generated in the WHERE section.
This is my query when I’ve selected a few “device_id” in the dropdown:
{
“request”: {
“url”: “api/ds/query”,
“method”: “POST”,
“data”: {
“queries”: [
{
“refId”: “A”,
“datasource”: {
“type”: “mysql”,
“uid”: “e387d16b-7f41-4304-9cec-757555016605”
},
“rawSql”: “SELECT\r\n $__timeGroupAlias(timedate, 10m),\r\n AVG(posicion) AS posicion\r\nFROM xxxx_q2.med_ncu\r\nWHERE $__timeFilter(timedate)\r\nAND device_id IN (‘1001’,‘10012’,‘10017’,‘10020’)\r\nGROUP BY device_id, $__timeGroup(timedate, 10m)\r\nORDER BY timedate ASC”,
“format”: “time_series”,
“datasourceId”: 4,
“intervalMs”: 600000,
“maxDataPoints”: 1508
}
],
“range”: {
“from”: “2023-05-24T06:07:49.986Z”,
“to”: “2023-05-25T06:07:49.987Z”,
“raw”: {
“from”: “now-24h”,
“to”: “now”
}
},
“from”: “1684908469986”,
“to”: “1684994869987”
},
“hideFromInspector”: false
},
“response”: {
“results”: {
“A”: {
“status”: 200,
“frames”: [
{
“schema”: {
“refId”: “A”,
“meta”: {
“typeVersion”: [
0,
0
],
“executedQueryString”: “SELECT\r\n UNIX_TIMESTAMP(timedate) DIV 600 * 600 AS "time",\r\n AVG(posicion) AS posicion\r\nFROM ignis_q2.med_ncu\r\nWHERE timedate BETWEEN FROM_UNIXTIME(1684908469) AND FROM_UNIXTIME(1684994869)\r\nAND device_id IN (‘1001’,‘10012’,‘10017’,‘10020’)\r\nGROUP BY device_id, UNIX_TIMESTAMP(timedate) DIV 600 * 600\r\nORDER BY timedate ASC”
},
“fields”: [
{
“name”: “Time”,
“type”: “time”,
“typeInfo”: {
“frame”: “time.Time”,
“nullable”: true
}
},
{
“name”: “posicion”,
“type”: “number”,
“typeInfo”: {
“frame”: “float64”,
“nullable”: true
}
}
]
},
“data”: {
“values”: [
[
1684908600000,
1684908600000,
1684908600000,
1684908600000,
1684909200000,
1684909200000,
1684909200000,
1684909200000,
1684909800000,
1684909800000,
1684909800000,
1684909800000,
(MANY MORE VALUES THAT I’VE DELETED FOR POST)
]
]
}
}
],
“refId”: “A”
}
}
}
}
Use transformation tab to select time series and then select multi frame
Thanks yosiasz, but doesn’t works
I’m doing it right?
You sure are. What data type and timezone is your timedate column?
Is device_id column an integer or a string data type?
Can you provide some sample data in csv format like
timedate,device_id,posicion
2023-05-25 07:22:26,1001,23.5
etc
timedate is datetime
device_id is integer
posicion is float
can I send to you a csv file?
Cool. Just post your csv here. open it in notepad++, copy and paste here like I mentioned ealier
timedate,device_id,posicion
2023-05-25 07:22:26,1001,23.5
device_id;timedate;posicion
1001;01/01/2023 0:00;10.98
1002;01/01/2023 0:00;10.93
1003;01/01/2023 0:00;10.82
1004;01/01/2023 0:00;10.99
1005;01/01/2023 0:00;11.06
1006;01/01/2023 0:00;11.02
1007;01/01/2023 0:00;11.1
1008;01/01/2023 0:00;11.04
1009;01/01/2023 0:00;9.1
1010;01/01/2023 0:00;11.05
1011;01/01/2023 0:00;11.0
1012;01/01/2023 0:00;11.2
1013;01/01/2023 0:00;9.62
1014;01/01/2023 0:00;11.18
1015;01/01/2023 0:00;11.12
1016;01/01/2023 0:00;11.14
1017;01/01/2023 0:00;9.04
Works for me. You need to make sure the timestamp is UTC
Maybe I didn’t explain myself correctly.
I don’t need to see the “device_id” in the graph. I need to see a line of “timedate” for each “device_id” that I select in the variable.
If I show the “device_id” in the chart, I see two lines that do not represent the “position” values correctly, whether I select only one value in the dropdown or if I select several values.
