Variable Difference over time (PostgreSQL, Grafana)

Question

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:

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:

1. What do I actually have to write instead of interval to manipulate the time?
2. "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
3. Is there maybe a better way to do this?

Thanks =) Sam

Answer 1

You can use a window function to do this:

CREATE TABLE public.data (
    datetimeutc timestamp without time zone,
    a integer
);

INSERT INTO public.data VALUES ('2024-05-14 14:00:00', 10);
INSERT INTO public.data VALUES ('2024-05-14 13:00:00', 12);
INSERT INTO public.data VALUES ('2024-05-14 12:00:00', 15);
INSERT INTO public.data VALUES ('2024-05-14 11:00:00', 8);
INSERT INTO public.data VALUES ('2024-05-14 10:00:00', 10);


WITH src AS(select datetimeutc as time, a, lag(a) OVER() as prev_a  
    FROM data ORDER BY datetimeutc desc) 
SELECT time,a,a-prev_a 
FROM src; 

See the documentation for lag(). The default offset is 1 which seems to be what you want.

Documentation for CTEs is here