Query the postive change per day

Telegraf
1

Hi there.

I want to determine the postive (value) change per day.

I’m tracking the output of a counter in a pulse sensor. Sometimes the counter resets, which messes up my queries when I group by day. I’ll show you what I’ve tried.

Let’s say I have the following data:

> select value from "autogen"."pulsesensor"
name: pulsesensor
time                value
----                -----
1520679600000000000 50001
1520697600000000000 50002
1520715600000000000 50003
1520733600000000000 50004
1520751600000000000 50005
1520769600000000000 50006
1520787600000000000 50007
1520805600000000000 50008
1520823600000000000 50009
1520841600000000000 50010
1520859600000000000 1
1520877600000000000 2
1520895600000000000 3

day 1 = [50001, 50002, 50003]
day 2 = [50004, 50005, 50006, 50007, 50008]
day 3 = [50009, 50010, 1, 2]
day 4 = [3]

The counter resets on day 3, and starts counting from 1.

The following query works for day 1, 2 and 4:

> select spread(value) as value from "autogen"."pulsesensor" group by time(1d) ;

name: pulsesensor
time                value
----                -----
1520640000000000000 2         # day 1
1520726400000000000 4         # day 2
1520812800000000000 50009     # day 3
1520899200000000000 3         # day 4

Day 3 gives the correct result for a spread function, but I actually want 2 as a result.

I cannot use derivative in combination with group by because then I have to use a nested InfluxQL function, which in turn loses accuracy. Especially on day 3.

I could do something like this:

> select "x" from (select last(value) - first(value) as "x" from "autogen"."pulsesensor" group by time(1d)) where "x" > 0
name: pulsesensor
time                x
----                ----
1520640000000000000 2  # day 1
1520726400000000000 4  # day 2
1520899200000000000 3  # day 4

but then I lose day 3.

Now I found cumulative sum, which almost does what I want.

> select cumulative_sum(last(value)) as "x" from "autogen"."pulsesensor" group by time(1d)
name: pulsesensor
time                x
----                ----
1520640000000000000 50003   # day 1
1520726400000000000 100011  # day 2
1520812800000000000 100014  # day 3
1520899200000000000 100021  # day 4
1520985600000000000 100029  # ?

If I had simply reset the counter to 0 every time I inserted a new value in the database, it would have worked perfectly. Unfortunately my sensor has no idea of when its values are extracted, so I cannot alter how it functions.

Does anyone know how I could query my data to get the positive value change per day?

TL;DR. I need a query to get the positive value change per day

2

I found a solution/workaround here.