Discrete Derivative Revisited: how to get dt

Source

In the last post, I calculated the discrete derivative of a counter variable's measurements, and I took the short cut by dividing the value's difference — that is, dx — simply by 300 instead of calculating the correct value of dt. This obviously needs to be fixed.

So, the last version of our statement looked like 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.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;

Getting the difference for the measured_at column works just like for all the other columns, by using the lag function and substracting its result from the current measured_at column:

select
  ...,
  energeh.measured_at - lag(energeh.measured_at, 1) over (order by measured_at) as measured_at_diff,
  ...

The full query:

select
  energeh.check_id,
  energeh.measured_at,
  energeh.measured_at - lag(energeh.measured_at, 1) over (order by measured_at) as measured_at_diff,
  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;

Which returns a result such as this (some columns snipped):

 check_id |      measured_at       | measured_at_diff
----------+------------------------+-------------------
      641 | 2015-05-17 02:00:00+02 |
      641 | 2015-05-18 02:00:00+02 | 1 day
      641 | 2015-05-19 02:00:00+02 | 1 day
      641 | 2015-08-14 02:00:00+02 | 1 day
      641 | 2015-08-15 02:00:00+02 | 1 day
      641 | 2015-08-15 18:55:00+02 | 16:55:00
      641 | 2015-08-15 19:00:00+02 | 00:05:00
      641 | 2015-08-15 19:05:00+02 | 00:05:00
      641 | 2015-08-15 19:10:00+02 | 00:05:00
      641 | 2015-08-15 19:15:00+02 | 00:05:00
      641 | 2015-08-15 19:20:00+02 | 00:05:00

This is a pretty good start, because it gives us the correct values. Unfortunately, if we now replace the hardcoded / 300 in the above statement with the same definition, we get an error:

ERROR:  operator does not exist: double precision / interval
LINE 7: ...runc( 'day', measured_at ) order by measured_at)) / (energeh...
                                                            ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

So, we need to convert the measured_at_diff column to something that can be divided correctly, for instance, a float holding the number of seconds in that interval. Luckily, the EXTRACT function can do that for us:

select
  ...,
  EXTRACT( EPOCH FROM energeh.measured_at - lag(energeh.measured_at, 1) over (order by measured_at) ) as measured_at_diff,
  ...

Which yields:

 check_id |      measured_at       | measured_at_diff
----------+------------------------+------------------
      641 | 2015-05-17 02:00:00+02 |
      641 | 2015-05-20 02:00:00+02 |            86400
      641 | 2015-08-14 02:00:00+02 |            86400
      641 | 2015-08-15 02:00:00+02 |            86400
      641 | 2015-08-15 18:55:00+02 |            60900
      641 | 2015-08-15 19:00:00+02 |              300
      641 | 2015-08-15 21:10:00+02 |              300
      641 | 2015-08-15 21:15:00+02 |              300
      641 | 2015-08-16 02:00:00+02 |            17100
      641 | 2015-08-16 06:25:00+02 |            15900
      641 | 2015-08-16 06:30:00+02 |              300
      641 | 2015-08-18 19:25:02+02 |              300
      641 | 2015-08-18 19:30:03+02 |              301
      641 | 2015-08-18 19:35:02+02 |              299
      641 | 2015-08-18 19:40:02+02 |              300

The irregularity of these values also explains why this is even interesting. We can't just hope that the interval will always be correct, because sometimes it just won't be, and there's even a good reason for it: At night, the solar inverter doesn't deliver any results, because it goes to sleep when the sun is down.

Anyway, now that we have the interval represented as a number of seconds, we can finally use it in the other parts of our statement:

select
  energeh.check_id,
  energeh.measured_at,
  EXTRACT( EPOCH FROM (energeh.measured_at - lag(energeh.measured_at, 1) over (order by measured_at))) as measured_at_diff,
  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)) / EXTRACT( EPOCH FROM (energeh.measured_at - lag(energeh.measured_at, 1) over (order by measured_at))) 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)) / EXTRACT( EPOCH FROM (energeh.measured_at - lag(energeh.measured_at, 1) over (order by measured_at))) 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;

The sheer length and copy-pasta of it all is starting to get to me, though...