Forward looking DIFFERENCE function

InfluxDB 2 Dashboards
1

I have a series of points that denote the energy in joules I want to convert this to power so that energy used from 7:00 to 7:15 would be stored with a timestamp of 7:00. Using the DIFFERENCE function calculates the difference relative to the last point. So energy used from 7:00 to 7:15 is stored as 7:15. I need it to be 7 not 7:15.

Could anyone give me some ideas how I can produce the data I want?

Example:

energy joules=101378708 1499410800000000000
energy joules=101379059 1499411700000000000
energy joules=101390839 1499412600000000000
energy joules=101408990 1499413500000000000
energy joules=101427152 1499414400000000000

SELECT DIFFERENCE(joules) / ELAPSED(joules, 1s) AS watts FROM energy

Gives me

time                 watts
----                 -----
2017-07-07T07:15:00Z 0.39
2017-07-07T07:30:00Z 13.088888888888889
2017-07-07T07:45:00Z 20.16777777777778
2017-07-07T08:00:00Z 20.18

But I want

time                 watts
----                 -----
2017-07-07T07:00:00Z 0.39
2017-07-07T07:15:00Z 13.088888888888889
2017-07-07T07:30:00Z 20.16777777777778
2017-07-07T08:45:00Z 20.18
2

@gambrose Have you tried adding a GROUP BY time(15m)?

3

@jackzampolin, yeah I tried that

SELECT DIFFERENCE(MAX(joules)) / ELAPSED(MAX(joules), 1s) AS watts FROM energy WHERE time > '2017-07-07T00:00:00Z'  GROUP BY time(15m)

Gives me the same results

If I set and offset_interval using advanced group by syntax

SELECT DIFFERENCE(MAX(joules)) / ELAPSED(MAX(joules), 1s) AS watts FROM energy WHERE time > '2017-07-07T00:00:00Z'  GROUP BY time(15m, -14m)

I’m able to move the times to be close to where they need to be

time                 watts
----                 -----
2017-07-07T07:01:00Z 0.39
2017-07-07T07:16:00Z 13.088888888888889
2017-07-07T07:31:00Z 20.16777777777778
2017-07-07T07:46:00Z 20.18

But if I try 15 mins back

SELECT DIFFERENCE(MAX(joules)) / ELAPSED(MAX(joules), 1s) AS watts FROM energy WHERE time > '2017-07-07T00:00:00Z'  GROUP BY time(15m, -15m)

It seams to ignore the 15 mins and behaves the same as no offset_interval