Plot long format data

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!

This topic was automatically closed after 365 days. New replies are no longer allowed.