Hello Grafana community,
I’m trying to calculate the duration interval between the “Now/d” (start of today) and “Now” (current time) timestamps using a FLUX query, but I haven’t been able to figure it out. I’m using the following images:
grafana/grafana-oss:11.0.0-ubuntuinfluxdb:2.7.10I apologize if this has been asked before. Any help would be appreciated!
I appreciate any help you can provide.
Welcome @federicocusot to the Grafana forum.
This should give you the total seconds elapsed since the start of today until now.
import "date"
startOfDay = date.truncate(t: now(), unit: 1d)
from(bucket: "your-bucket")
|> range(start: startOfDay)
|> filter(fn: (r) => r["_measurement"] == "your-measurement")
|> filter(fn: (r) => r["_field"] == "your-field")
|> elapsed()
|> map(fn: (r) => ({r with elapsed: float(v: r.elapsed)}))
|> sum(column:"elapsed")
|> yield(name: "elapsed_seconds_today")
Hello @grant2, thank you for the warm welcome!
I followed your query exactly as you described, and it came very close to what I needed. However, I’m encountering a few issues that I hope you might help me solve.
Here’s a quick overview of what I’m trying to achieve (apologies for the shaky handwriting, I was using my mouse):
What I’m aiming for is to get the elapsed duration from the start of the day (“Now/d”) until the current time (“Now”), either in minutes or seconds. The exact unit doesn’t matter too much, but I haven’t managed to get it quite right yet.
Do you think it’s possible to achieve this? I understand it’s a simple calculation, but I just want to retrieve the duration in the correct format. Any suggestions you have would be greatly appreciated!
Thanks again for your prompt response, and sorry if I missed something obvious.
Just to make sure the query is calculating the # of seconds correctly, if you clear out the units in the right-hand pane, do you get a value like this?
Note the above (57000 seconds) is 15.83 hours. Where I am (Eastern Time Zone in the US), it’s 10:50 AM, so only 10.83 hours have elapsed. The reason for the 5-hr difference is that my Influx timestamps (and probably yours) are in UTC (and EST is currently 5 hours behind UTC). You’d have to add the appropriate correction for your local timezone (many threads on that subject here and on the InfluxDB forum).
Oh, I got what you are saying regarding this.
I am at UTC-3 tz.
And yes, if I take off the units, I get a number like this one:
But this means 783 seconds are 13.05 minutes. and I have a difference from
00:00 to 13:00 approximately Should it be greater?
I apologize maybe I am doing something wrong …
There must be something else affecting the result. You could try running the same query in Influx Data Explorer and see what value you get.
In Grafana, make sure you do not have any of the Query Options set and the settings on the right all make sense.
I agree, this should work.
This is the result of the same query at Influx Data Explorer:
I wasn’t expecting this level of difficulty at having the time interval or the duration interval between
These two timestamps:
PD: Is it necessarily dependent on a database or a table? Or is the data proper in the Grafana environment? This is something I don’t really understand.
So this line means that Influx’s time picker (and Grafana’s time picker) will not be used at all.
Perhaps the response you get is wrong because it’s looking at your data and the timestamps are affecting the result. Do you have data from 00:00:00 today stored? Maybe you only have a reading from 783 seconds ago?
Can you remove the _field filter and see what results you get?
Hmmm I see.
If I remove the “_field” filter I receive this:
hand if I remove both filters I receive this:
Which btw has the following content
elapsed {_field="state", _start="2024-12-13 00:00:00 +0000 UTC", _stop="2024-12-13 17:39:46.041840646 +0000 UTC"}
25140
I imagine that 17:39:46.041840646 is at UTC, right now it’s 14:42 at UTC-3 so it would make sense.
Here is what I got with other bucker:
import "date"
startOfDay = date.truncate(t: now(), unit: 1d)
from(bucket: "production_bucket")
|> range(start: startOfDay)
|> filter(fn: (r) => r["_measurement"] == "foo")
|> filter(fn: (r) => r["_field"] == "yolo")
|> elapsed()
|> map(fn: (r) => ({r with elapsed: float(v: r.elapsed)}))
|> sum(column:"elapsed")
|> yield(name: "elapsed_seconds_today")
Result
and on Influx Explorer, the same query returns the following
Do you have data from yesterday? Acc. to the documentation for the elapsed function…
For each input table,
elapsed()returns the same table without the first row (because there is no previous time to derive the elapsed time from) and an additional column containing the elapsed time.
Which means (perhaps) that it’s counting from the first available record from today, and not from 00:00:00.
Hmmm seems a bit more complicated than I thought.
I’ve run into a bit of a challenge.
Let’s say I have the following query:
from(bucket: "planification")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "production")
|> filter(fn: (r) => r["_field"] == "production_start_time")
|> last()
|> yield(name: "last")
This query returns something like the following:
iIt’s just a time in the format HH:MM.
Now, what I want to do is calculate the duration between this time and the current time (now). I want to know how long it’s been since the production_start_time until now. The idea is to get this duration, potentially in minutes or seconds, and use it for further analysis.
I feel like there’s a way to do this in Grafana, but I’m a bit stuck at the moment and lacking ideas on how to approach this. I want this to be my upper bound if you know what I mean.
So the interval is always the result from the previous query, till “now”.
So I can later use that duration.
There must be a way but I am stuck with it and lack ideas.
Sorry for my long texts.
Here is a diagram Where I explain it a bit better. Sorry for my lack of formalities.
Here the first time will reset everyday, but the last one will continue to increase of course, since, its the “now” variable
Oh wow! Now I understand. You created a field in your database called production_start_time which is something IN ADDITION TO the regular timestamps that Influx uses. So in your diagram, you are trying to compare the duration between a field value and a time value. Not quite sure how to do that, but first, it would be ideal to convert your field value (08:04) to an epoch timestamp. Then you could use a map function or even Grafana to calculate the difference between the field value and now.
Is there only one “production_start_time” every day (i.e. every 24 hours)? Maybe you could share your reasoning why you chose to create a field to capture this time value, and if you could instead store it as an epoch timestamp.
Perhaps a better way to record the production start time is to have boolean field called “RunningState” with a 1 or 0 to indicate when the machine (or whatever it is) is running or not. Then, every time the field switches from 0 to 1, that would be the start, correct?
