#influxdb

hmm, we really need today()

time >= now() - 1d ?

Lydia_K: that's unfortunately not the same

it returns part of yesterday as well

True

it would work if we could do now() - time(now())

So, Lydia_K, any suggestions for getting only today's values? And preferably (max) today's - (min) today's

That is a tricky one..

I know, I've been strugling with it for days.

Currently I'm thinking of doing it in bash

all my influx ends up being a DB for grafana on the front end, which nicely does the "today" thing already, but that's cause it just passes explicit time values.

I would probably do it in bash, then you can easily get yourself a start and end time and supply that.

Lydia_K: yeah, https://0bin.net/paste/73LruCWepqTGU2QB#5eVrbYQ... is what I have

I'm not an influxdb expert, but I can't think of a way offhand to get that out of influx without specifying the start/stop times (or at least the start)

but it feels like hacking something that shouldn't have to be hacked :)

That looks pretty good.

yeah, well

I'll probably re-inject these values into influx and then retrieve them for my single stat

But it feels wrong

If you are using Grafana on the front end then it can get you "today" time slice data you are looking for.

But I mean, that will affect the entire dashboard.

I wish grafana or influxdb supported time shift for one query

grafana can do time shift for an entire graph

Lydia_K: hmmm, does today in Grafana calculate the difference between the first and the last point? Beacuse the series is "Total"

and I saw a grafana example that overlayed current values with 24h-shifted values, but it relied on a feature of another DB engine

nicolas17: there's also a proxy for influx on github that does the same

nicolas17: https://github.com/GoshPosh/grafana-meta-queries

That'll let you timeshift a single query

blizzkid: I'm pretty sure grafana just provides time >= 2017-12-6 00:00:00

Lydia_K: is that the proxy blizzkid meant or does it work in a different way?

I might be wrong though.

meta quieries lets you use the results of one query in another, or timeshift a query

I've found it to be super useful in getting things done that influx doesn't support at this time.

the feature I want the most, and which metaqueries might have, is run a different query for times older than X

Lydia_K: even with it supporting time >= 2017-12-6 00:00:00, that doesn't calculate anything, does it?

Like I said, my series is total consumption

so I need to calculate todays consumption by taking the point at midnight and the last one

nicolas17: Yes, it timeshifts

so I can have a single graph that uses "select max(x)" for current data and "select max_x" from a different RP for older data

I've not actually used the timeshift feature, but that's what it's supposed to do as to my understanding.

blizzkid: Ahhh, let me think about that for a minute

oh time shift is super useful but it's a different thing

Lydia_K: have a look at the bash script, it's quite self-explanatory ;)

time shift is useful if you want a query doing "select x" for current data, and you want to add another graph with "select x" 24 hours shifted, so you can visually compare current values with yesterday's and notice anomalies

something completely different is when you have a retention policy that deletes data after 24 hours, and a continuous query that aggregates older data into a different RP, and you want to see data from both in the same graph so you can just scroll backwards in time

oh oh, I totally misunderstood what you were looking for.

Lydia_K: I want the time shift too for something else, so your link is still useful :)

Sorry, company holiday party last night, out late drinking heavily -.-

blizzkid: I see what you are trying to do now, Umm, There *might* be a way to do that, let me try something. (Otherwise I've achived the same idea via the meta query plugin)

nicolas17: I have wondered about that too, downsampling in general doesn't work the way I want it to :(

currently I'm not downsampling or expiring data

and I better do something about it before I run out of disk space

retention: FOREVER

I don't particularly care about CPU usage every 5 seconds going back months :P

Yeah, I've been writing a script to set up basic retention policies and downsampling continuous queries cause there's so many things to do to set that all up.

Lydia_K: it seems SELECT difference(max("value")) FROM "SDM530A_Total_kWh" WHERE time >= now() - 1d GROUP BY time(1d) fill(null) comes pretty close

Yeah, even with subqueries I can't get this to work because it requires arithmetic on the two results.

I feel like I'm close, but it might not be possible

Take a look at the meta query plugin, that will do it for you for sure

hmm, the query I gave is absolutely very close

you have have a query that gets the first value, a query that gets the last value, then the meta query that subtracts one from the other.

As in I have the same result now as my bash script

SELECT difference(max("value")) FROM "SDM530A_Total_kWh" WHERE time >= now() - 1d GROUP BY time(1d) fill(null)

I do that same thing to calculate days till full on our storage based on the average consumption per day

gives the same outcome as my bash script for the moment

blizzkid: http://picpaste.com/gkrellShoot_12-06-17_153540...

Lydia_K: is that using influx only or with the meta query plugin?

That's the meta query plugin

The last query is where I divide one result by the other result.

I see. It looks like difference(max(value)) does the same for what I want

dividing is something else ofcourse

wb sandeen. FYI it seems I have found a working query

SELECT difference(max("value")) FROM "SDM530A_Total_kWh" WHERE time >= now() - 1d GROUP BY time(1d) fill(null)