I have a table “Data” containing the columns “DateTimeUTC” and “A”, where I want to calculate the difference in A over a certain time range and display this difference in a time series in Grafana.
This is how far I got:
<code>SELECT
"DateTimeUTC" as "time",
"A"-(select "A" from "Data" WHERE "DateTimeUTC" <= "time" - interval ORDER BY "DateTimeUTC" Desc LIMIT 1)
FROM
"Data"
</code>
<code>SELECT
"DateTimeUTC" as "time",
"A"-(select "A" from "Data" WHERE "DateTimeUTC" <= "time" - interval ORDER BY "DateTimeUTC" Desc LIMIT 1)
FROM
"Data"
</code>
SELECT
"DateTimeUTC" as "time",
"A"-(select "A" from "Data" WHERE "DateTimeUTC" <= "time" - interval ORDER BY "DateTimeUTC" Desc LIMIT 1)
FROM
"Data"
Unfortunately, this doesn’t work yet. Here are my questions:
- What do I actually have to write instead of interval to manipulate the time?
- “time” should be the value of the x-axis of the shown point, how do I get this in the subquery? In Grafana, I get the message that “time” is not known
- Is there maybe a better way to do this?
Thanks =)
Sam
New contributor
Sam is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.