Grafana 8.1.1 - Postgresql Exception :- long series must be sorted ascending by time to be converted

Question

I am trying to fetch metrics from postgresql (timeseries) database in Grafana (Ver 8) using below query .

Just wondering its throwing below exception :-

failed to convert long to wide series when converting from dataframe: long series must be sorted ascending by time to be converted

SELECT time, cpu_count,CASE WHEN step = 0 THEN 'Today' ELSE (-interval)::text END AS metric
FROM
-- sub-query to generate the intervals
( SELECT step, (step||'day')::interval AS interval FROM generate_series(0,3) g(step) order by interval asc) g_offsets 
JOIN LATERAL (
SELECT
-- adding set interval to time values
  time_bucket('15m',time + interval )::timestamptz AS time, avg(limit_cpu) AS cpu_count FROM cpu_model 

WHERE
  time BETWEEN $__timeFrom()::timestamptz - interval AND $__timeTo()::timestamptz - interval 
GROUP BY 1
ORDER BY 1,2 ASC
) l ON true

Would appreciate it if some one can help me to find the error or provide solution .

Hello Pravek what version of Grafana are you using? Searching for that error came up with a Grafana bug that had a long discussion. That was with Grafana version 8.0.3.. Have a quick google and check if it fits? I think there were bug reports for this. — greenweeds
– greenweeds, Commented Sep 7, 2021 at 9:10
Thanks greenweeds ! I am using v8.1.1 . Could you please advise if query can be modified a bit to get desired result. I am trying to get the metrics of current and previous days. — Pravek
– Pravek, Commented Sep 7, 2021 at 9:21
it's a wild guess, but maybe try putting ORDER BY 1 ASC at the end of your query? (so NOT inside a subquery) @Pravek — Attila Toth
– Attila Toth, Commented Sep 7, 2021 at 11:56
Can't exactly tell by the query that's in the OP, but if you are trying to fetch time series metrics, and you've selected the 'Format As: Timeseries' option, then your results must be in ascending order by time. Try using a table visualization and the "Format As: Table" option to see what the order of the data is. — Kevin Minehart
– Kevin Minehart, Commented Sep 8, 2021 at 16:23

valuebetclub.com · Accepted Answer · 2022-09-07 16:50:20Z

3

In my case, for some reason sorting the data by time ASC solved the issue. Grafana 's error was correct.

SELECT
  time AS "time",
  pair,
  price as value
FROM currency_pair_price
WHERE
  time/1000 >= 1662481845 AND time/1000 <= 1662568245
  AND pair = 'BTCBUSD'
ORDER BY time ASC;

answered Sep 7, 2022 at 16:50

valuebetclub.com

313 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Grafana 8.1.1 - Postgresql Exception :- long series must be sorted ascending by time to be converted

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related