- What are you trying to achieve?
Plot long-format data (The Wide and Long Data Format for Repeated Measures Data - The Analysis Factor) of machines in a timeseries, where each machine is defined by a machine_id on each row.
This is explained in (Data frames | Grafana documentation), it’s mentioned that “Grafana can detect and convert data frames in long format into wide format.” but there’s no detail on how or when this happens?
DB is Postgres 13 with Timescale
My schema is:
machine_id | bucket | current1_avg | current1_max | current2_avg | current2_max | current3_avg | current3_max
------------+---------------------+--------------------+--------------------+--------------------+-------------------+--------------------+--------------------
6 | 2021-07-02 04:02:30 | 9.013491230644206 | 16.33757500636925 | 2.7078713878804237 | 6.564167444134483 | 3.4991609021738768 | 4.5416008510695605
6 | 2021-07-02 04:02:35 | 13.670205739773351 | 19.68279142619842 | 5.003477723647649 | 9.392061674372947 | 2.3126696740262496 | 3.6936120391571237
6 | 2021-07-02 04:02:40 | 4.383233167794089 | 8.102303176662744 | 3.8677409199023103 | 6.46442564149492 | 3.592973386999077 | 4.442744447320628
6 | 2021-07-02 04:02:45 | 7.5639609237601775 | 15.449588715823026 | 2.2777187467201117 | 4.014778264837702 | 2.248637114777811 | 4.8061654243851315
6 | 2021-07-02 04:02:50 | 4.8792232275873175 | 12.046895038764234 | 2.6044175478690974 | 4.760594619559768 | 2.6340195752558273 | 4.993361346126992
6 | 2021-07-02 04:02:55 | 9.14152708191655 | 19.851826514488167 | 6.589597551173142 | 9.886065992400148 | 3.2646351578410098 | 4.706124648762149
6 | 2021-07-02 04:03:00 | 6.292055951897254 | 16.1672
- How are you trying to achieve it?
SELECT
bucket AS "time",
current1_avg AS metric,
machine_id
FROM machine_current_sanitized
WHERE
$__timeFilter(bucket) AND
machine_id IN ($machines)
ORDER BY 1,2
-
What happened?
All machines are plotted as a single series
-
What did you expect to happen?
Each machine to have its own series or line, I’d like it looking like this
This seems like a really basic functionality, but I can’t seem to get it working - does no-one plot data like this from a SQL type DB? Thanks in advance for any guidance!