# Forward looking DIFFERENCE function

#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`