Discrete Derivative over time series data using PostgreSQL

I use FluxMon to query statistics from the inverter hooked up to our solar collectors. One of the values the inverter spits out is today's energy production in Wh. This value is reset every day and counts up to a certain value. Fair enough, but I'd be way more interested in the derivative of that value, which would give an indication of how much power is being produced per hour or so.

So first of all, we get the value for the etoday and the etotal variables like we did before:

select
  cm.check_id,
  cm.measured_at,
  avg(cm.value) filter (where sv.name = 'etoday') as etoday,
  avg(cm.value) filter (where sv.name = 'etotal') as etotal
from
  monitoring_checkmeasurement cm
  inner join monitoring_sensorvariable sv on variable_id=sv.id
where
  cm.check_id=641
group by
  cm.check_id,
  cm.measured_at
order by cm.measured_at ;

Result:

 check_id |      measured_at       |    etoday    |    etotal
----------+------------------------+--------------+--------------
      641 | 2015-08-15 18:55:00+02 |     34619.17 |  16529491.15
      641 | 2015-08-15 19:00:00+02 |     34803.77 |  16529674.78
                       ...
      641 | 2015-08-15 21:10:00+02 |        35528 |     16530398
      641 | 2015-08-15 21:15:00+02 |        35528 |     16530398
      641 | 2015-08-16 06:25:00+02 |            0 |     16530398
      641 | 2015-08-16 06:30:00+02 |            0 |     16530398
                       ...
      641 | 2015-08-16 07:25:00+02 |            0 |     16530398
      641 | 2015-08-16 07:30:00+02 |            0 |     16530398
      641 | 2015-08-16 07:35:00+02 |         0.99 |  16530398.99
      641 | 2015-08-16 07:40:00+02 |         3.98 |  16530401.98

You can clearly see the value increasing up to a certain point, then being abruptly reset (and there's a big hole in the measured_at timestamps too), and the process starting over again.

It looks like to get the derivative of this field, we need to apply the lag function, which is a window function and therefore requires a window to be defined. I have no idea how to do that, but luckily, windows can also be applied to other aggregate functions like avg, so I should be able to rewrite the above statement to do the same thing, but use a window instead.

After quite a lot of fiddling, I ended up with this:

select
  energeh.check_id,
  energeh.measured_at,
  energeh.etoday,
  lag(energeh.etoday, 1) over (partition by date_trunc( 'day', measured_at ) order by measured_at) as etoday_lag,
  energeh.etotal,
  lag(energeh.etotal, 1) over (order by measured_at) as etotal_lag
from (
  select
    cm.check_id,
    cm.measured_at,
    avg(cm.value) filter (where sv.name = 'etoday') as etoday,
    avg(cm.value) filter (where sv.name = 'etotal') as etotal
  from
    monitoring_checkmeasurement cm
    inner join monitoring_sensorvariable sv on variable_id=sv.id
  where
    cm.check_id=641
  group by
    cm.check_id,
    cm.measured_at
  order by cm.measured_at
) as energeh;

I'm not quite sure the subselect is actually necessary, but it's the best I could come up with so far. Anyway, now I get this result:

 check_id |      measured_at       |    etoday    |  etoday_lag  |    etotal    |  etotal_lag
----------+------------------------+--------------+--------------+--------------+--------------
      641 | 2015-08-15 18:55:00+02 |     34619.17 |              |  16529491.15 |
      641 | 2015-08-15 19:00:00+02 |     34803.77 |     34619.17 |  16529674.78 |  16529491.15
                       ...
      641 | 2015-08-15 21:10:00+02 |        35528 |        35528 |     16530398 |     16530398
      641 | 2015-08-15 21:15:00+02 |        35528 |        35528 |     16530398 |     16530398
      641 | 2015-08-16 06:25:00+02 |            0 |              |     16530398 |     16530398
      641 | 2015-08-16 06:30:00+02 |            0 |            0 |     16530398 |     16530398
                       ...
      641 | 2015-08-16 07:25:00+02 |            0 |            0 |     16530398 |     16530398
      641 | 2015-08-16 07:30:00+02 |            0 |            0 |     16530398 |     16530398
      641 | 2015-08-16 07:35:00+02 |         0.99 |            0 |  16530398.99 |     16530398
      641 | 2015-08-16 07:40:00+02 |         3.98 |         0.99 |  16530401.98 |  16530398.99

Note that I need two separate windows for etoday and etotal. This is because the etoday value is reset every day, but the etotal field is not. Hence, I must prevent the last value of etoday from being transferred into the new day, whereas the etoday value must be transferred. That's why the etoday_lag column is empty in the 2015-08-16 06:25:00+02 row above.

So now, getting the derivative is a pretty easy thing to do, just substract the values and divide by the interval (300 seconds in this case):

select
  energeh.check_id,
  energeh.measured_at,
  energeh.etoday,
  lag(energeh.etoday, 1) over (partition by date_trunc( 'day', measured_at ) order by measured_at) as etoday_lag,
  (energeh.etoday - lag(energeh.etoday, 1) over (partition by date_trunc( 'day', measured_at ) order by measured_at)) / 300 as etoday_diff,
  energeh.etotal,
  lag(energeh.etotal, 1) over (order by measured_at) as etotal_lag,
  (energeh.etotal - lag(energeh.etotal, 1) over (order by measured_at)) / 300 as etotal_diff
from (
  select
    cm.check_id,
    cm.measured_at,
    avg(cm.value) filter (where sv.name = 'etoday') as etoday,
    avg(cm.value) filter (where sv.name = 'etotal') as etotal
  from
    monitoring_checkmeasurement cm
    inner join monitoring_sensorvariable sv on variable_id=sv.id
  where
    cm.check_id=641
  group by
    cm.check_id,
    cm.measured_at
  order by cm.measured_at
) as energeh;

Result:

 check_id |      measured_at       |    etoday    |  etoday_lag  |     etoday_diff      |    etotal    |  etotal_lag  |     etotal_diff
----------+------------------------+--------------+--------------+----------------------+--------------+--------------+----------------------
      641 | 2015-08-15 18:55:00+02 |     34619.17 |              |                      |  16529491.15 |              |
      641 | 2015-08-15 19:00:00+02 | 34803.773333 |     34619.17 |     0.61534444333333 |  16529674.78 |  16529491.15 |    0.612099999996523
                       ...
      641 | 2015-08-15 21:10:00+02 |        35528 |        35528 |                    0 |     16530398 |     16530398 |                    0
      641 | 2015-08-15 21:15:00+02 |        35528 |        35528 |                    0 |     16530398 |     16530398 |                    0
      641 | 2015-08-16 06:25:00+02 |            0 |              |                      |     16530398 |     16530398 |                    0
      641 | 2015-08-16 06:30:00+02 |            0 |            0 |                    0 |     16530398 |     16530398 |                    0
                       ...
      641 | 2015-08-16 07:25:00+02 |            0 |            0 |                    0 |     16530398 |     16530398 |                    0
      641 | 2015-08-16 07:30:00+02 |            0 |            0 |                    0 |     16530398 |     16530398 |                    0
      641 | 2015-08-16 07:35:00+02 |         0.99 |            0 |               0.0033 |  16530398.99 |     16530398 |  0.00330000000074506
      641 | 2015-08-16 07:40:00+02 |         3.98 |         0.99 |  0.00996666666666667 |  16530401.98 |  16530398.99 |  0.00996666666741172
                       ...
      641 | 2015-08-16 08:20:00+02 |        37.94 |        28.98 |   0.0298666666666667 |  16530435.94 |  16530426.98 |   0.0298666666634381
      641 | 2015-08-16 08:25:00+02 | 47.933333333 |        37.94 |        0.03331111111 | 16530445.933 |  16530435.94 |   0.0333100000023842
      641 | 2015-08-16 08:30:00+02 | 52.966666667 | 47.933333333 |        0.01677777778 | 16530450.967 | 16530445.933 |   0.0167799999999503
      641 | 2015-08-16 08:35:00+02 | 56.973333333 | 52.966666667 |   0.0133555555533333 | 16530454.973 | 16530450.967 |   0.0133533333304028

Unfortunately, it's been a rainy day, so the energy production today totally sucks ass — but the query seems to work. I'd just like to improve the way the interval is being handled, using a hardcoded / 300 seems a bit hacky.