Plot derivative at correct time using influxdb/grafana

Question

I’m tracking my energy usage, amongst which a heat meter measuring usage in Joule. I want to calculate the heating power (in Watt), which works well with the DERIVATIVE() function.

The problem is that the derivative is returned on the latter of two timestamps describing the data, which when combined with the staircase plot of Grafana shows the derivative for the next time delta instead of the current.

How can I:

1. Make Influxdb show the derivative on the first timestamp of the data? (Or alternatively: shift timestamps of a query?)
2. Make Grafana plot staircase graphs extending the value left instead of right?

Graphical example

Textual example

The following heat meter readings in influxdb

08:00,    0 Joule
09:00, 3600 Joule
19:00, 7200 Joule

will give

09:00, 1.0 Watt (which refers to 08:00-09:00)
18:00, 0.1 Watt (which refers to 09:00-18:00)

which Grafana (as staircase) will plot as

08:00-09:00, Null (should be 1.0)
09:00-18:00, 1.0  (should be 0.1)
18:00-,      0.1  (should be Null)

Answer 1

I solved my own problem by interpolating the data using GROUP BY() and fill(linear) before calculating the derivative.

Create sample data

insert testenergy val=0     1565503200
insert testenergy val=3600  1565506800
insert testenergy val=7200  1565510400
insert testenergy val=10800 1565514000
insert testenergy val=14400 1565550000
insert testenergy val=18000 1565557200

yields

SELECT last("val") as energy FROM "testenergy" WHERE $timeFilter GROUP BY time($__interval) fill(null)

time                 energy
----                 ------
2019-08-11T06:00:00Z 0
2019-08-11T07:00:00Z 3600
2019-08-11T08:00:00Z 7200
2019-08-11T09:00:00Z 10800
2019-08-11T19:00:00Z 14400
2019-08-11T21:00:00Z 18000

DERIVATIVE() query that does not work:

SELECT derivative(last("val"), 1s) as power_wrong FROM "testenergy" WHERE $timeFilter GROUP BY time($__interval) fill(null)

The problem is that InfluxDB calculates the derivative of (t_n, t_n+1) at time t_n+1, which Grafana using staircase plot then displays from (t_n+1, t_n+2):

time                 power_wrong
----                 -----------
2019-08-11T07:00:00Z 1
2019-08-11T08:00:00Z 1
2019-08-11T09:00:00Z 1
2019-08-11T19:00:00Z 0.1
2019-08-11T21:00:00Z 0.5

A solution is to interpolate the data in InfluxDB before calculating the derivative:

SELECT derivative(mean("val"), 1s) as power_fix FROM "testenergy" WHERE $timeFilter GROUP BY time($__interval) fill(linear)

yields

time                 power_fix
----                 ---------
2019-08-11T07:00:00Z 1
2019-08-11T08:00:00Z 1
2019-08-11T09:00:00Z 1
2019-08-11T10:00:00Z 0.1
2019-08-11T11:00:00Z 0.1
2019-08-11T12:00:00Z 0.1
2019-08-11T13:00:00Z 0.1
2019-08-11T14:00:00Z 0.1
2019-08-11T15:00:00Z 0.1
2019-08-11T16:00:00Z 0.1
2019-08-11T17:00:00Z 0.1
2019-08-11T18:00:00Z 0.1
2019-08-11T19:00:00Z 0.1
2019-08-11T20:00:00Z 0.5
2019-08-11T21:00:00Z 0.5