Unable to display requests per second in Grafana

[cburu]

Hi all. We’re currently able to query our Timescale database for metrics and they show up correctly in the table. See below.

SELECT to_timestamp(start_time / 1000000.0 ) AS "time",
(
	CASE
		WHEN value >= lag(value) OVER w
			THEN value - lag(value) OVER w
		WHEN lag(value) OVER w IS NULL THEN NULL
		ELSE value
	END
) AS "requests"
FROM metricstable
WHERE to_timestamp(start_time / 1000000.0) > NOW() - INTERVAL '1 day'
WINDOW w AS (ORDER BY start_time)
ORDER BY start_time;

Resulting table:

However, this data is not displaying on Grafana:

How can I configure Grafana or tweak my SQL query to make it work with Grafana? This is to display the requests per time interval given.

Edit to add: whether the first line of the SQL statement is as is or as SELECT start_time AS "time" does not alter the results in Grafana.

Thanks!

[mattabrams]

Hello and welcome to the forum, @cburu

To be clear, it looks like your query is returning your data, it’s just not formatted correctly to display in a graph. Is that correct?

Please share your raw, unformatted data using the inspect drawer like this.

this might have to do with your timestamp formatting

[cburu]

Thank you @mattabrams for the welcome! Figured it out and it’s as you said — we were using the wrong data for the “date” and it wasn’t the date at all. It was the time the server booted up, so it was all the same “time”.

Now it’s looking great in Grafana. Appreciate the reply!