Discrete Derivative Revisited: how to get dt

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...