1

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 .

Pravek
  • 11
  • 1
  • 3
  • 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 Sep 07 '21 at 09: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 Sep 07 '21 at 09: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 Sep 07 '21 at 11:56
  • 1
    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 Sep 08 '21 at 16:23
  • @KevinMinehart that did the trick for me! Nice one! – Matt Barry Jan 12 '22 at 20:23

1 Answers1

2

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;
valuebetclub.com
  • 21
  • 3