Is there a way of computing numerical integration on PostgreSQL?

Question

I've got a table with the following columns: timestamp, name and activePower. I wanted to compute power consumption based on that and add it to a Grafana line chart.

Right now, what I'm doing is a cumulative sum, like the following:

SELECT
  "timestamp" as time,
  "name", sum("activePower")
    OVER(
      PARTITION BY "name"
      ORDER BY "timestamp"
    ) AS cumulative_sum
FROM   main

Unfortunately, the intervals of timestamp are not regular, and I wanted to do something like a numerical integration (using trapezoidal rule or something).

Answer 1

You can use lag() and sums. Something like this:

select m.*,
       sum( (extract(epoch from timestamp) - extract(epoch from prev_timestamp)) * (activePower + prev_activePower) / 2 ) as activePower_seconds
from (select m.*,
             lag(timestamp) over (partition by name order by timestamp) as prev_timestamp,
             lag(activePower) over (partition by name order by activePower) as prev_activePower
      from main m
     ) m;

This does the integral using seconds for the horizontal axis.