InfluxDB and Grafana graph using midnight as 0 on Y-axis derivative
<p>I am graphing with Grafana (2.6.0) and I have an InfluxDB (0.10.2) database with the following data in it:</p>

<pre><code>&gt; select * from "WattmeterMainskwh" where time &gt; now() - 5m
name: WattmeterMainskwh
-----------------------
time                value
1457579891000000000     15529.322
1457579956000000000     15529.411
1457580011000000000     15529.425
1457580072000000000     15529.460
1457580135000000000     15529.476
 ...etc...
</code></pre>

<p>This data collects my household kilowatt usage as measured by a kWH gauge that steadily increments the usage value across months or years. I cannot easily reset the counter, nor do I wish to do so.</p>

<p><strong>My goal is to create a graph that shows my daily kWH use over 24 hour periods starting at midnight, or at a minimum showing relative kWH over the interval displayed.</strong> This type of graph would be useful in many other circumstances as well where I could imagine "errors across the day" or "visitors since opening time" or "BGP resets per calendar week" were useful but the collection counter was not reset to zero upon the reset or turn-over of the time interval. This kind of counting is actually quite common in my experience.</p>

<p>This graph works, but doesn't show me what I'm looking for:</p>

<pre><code>SELECT derivative(mean("value")) FROM "WattmeterMainskwh" WHERE $timeFilter GROUP BY time($interval) fill(null)
</code></pre>

<p>That graph just shows the difference between one sample and the previous sample. What I want is a steadily increasing line starting from the left side of the graph and increasing towards the right side of the graph, with zero as the bottom of the Y axis, and the graph starting at zero at the farthest left X value.</p>

<p>This graph works too and shows me the correct curve, but it's off by fifteen thousand or so. So far, it's the closest to what I want but since this is an ever-increasing counter that can't be reset I need to subtract some from the Y axis. Ideally, I'd like to subtract whatever the value was at the previous midnight from each sample to get a relative number based on a day instead of an absolute based on all time.</p>

<pre><code>SELECT sum("value") FROM "WattmeterMainskwh" WHERE $timeFilter GROUP BY time($interval) fill(null)
</code></pre>

<p>And here's the graph from that previous statement:</p>

<p><a href="http://i.stack.imgur.com/Mxkqz.png" rel="nofollow">Graph that is off by 15k</a></p>

<p>This attempt didn't work - I apparently can't take a sum of a derivative group:</p>

<pre><code>SELECT sum(derivative(mean("value"))) FROM "WattmeterMainskwh" WHERE $timeFilter GROUP BY time($interval) fill(null)
</code></pre>

<p>This doesn't work, either - I can't perform functions within "derivative":</p>

<pre><code>SELECT derivative(sum("value")-first("value")) FROM "WattmeterMainskwh" WHERE $timeFilter GROUP BY time($interval) fill(null)
</code></pre>

<p>Of course, I could just create a new value that had calculations applied to it before I wrote it into InfluxDB, but that seems to me to be a data-redundant and sloppy way to solve this problem, as well as being quite inflexible if I want to look at other intervals on a whim. I'm hoping that there is some way to do this more elegantly within the combination of InfluxDB &amp; Grafana, but I'm just not able to find it with the search terms I've used or the thinking I've put towards interpreting the documentation.</p>

<p>Is this type of graph even possible with InfluxDB/Grafana? As far as I can tell a continuous query is not a solution, and the lack of nested SELECTs makes even the hackish ways of doing this not obvious to me.</p>

<p>BONUS: It would be really great to have the graph show midnight every night as a "zero" location, instead of "zero" being the first point in the displayed interval, so looking at five days of normal data would show five distinct "waves" of increasing daily aggregate energy usage, with the wave Y value going back down to zero at 12:00:01 on each day.  But I'll take whatever I can get.</p>