How to calculate the time range?

Hello Good day, I am fresh new bee of Influx DB, I am doing some hash board and would like to use the selected time range to calculate the usage in percentage format, how do I get he range duration given the fact I know the range start and end, just use start -end or something else, as the select time could be changed dynamically.
Any hints will be great appreciate.

Richard

Hi @RichardL
Are you using Flux or InfluxQL?

I am using Flux at this time, not even touch influxSQL.

@RichardL
I am not sure I understand your question. If you have a time range selected (e.g. 1 hour), and you get a plot like this:

and then you select 12 hours, and you get a plot like this:

Specifically, can you explain this part of your question?

calculate the usage in percentage format, how do I get he range duration given the fact I know the range start and end

hi grant, sorry about the late replay , was tied with some other things, I appreciate your quick response, I have a data source which include the the total operation time, it is always cumulate value, at any given time, say I select past two hours from now or certain time range t1 to t2, then I got t1’s total time tToal1 and t2’s total time tTotal2, then I know the total operation is tTotal2-tTotal1, then system operation ratio is (tTotal2-tTotal1)/(t2-t1)*100%. I need to put this in dashboard or my iot center, allow the user select the range dynamically.

I still do not completely understand the question. What is the formula to calculate tTotal1 and tTotal2? Please spell it out as much as possible, as if you were explaining this to your grandmother.

@grant1 , Sorry about the late, was on march break vacation. just get back. in general my issue is I have information of certain select time period, lets say from 2023-03-08 10:30:23 to 2023-03-20 10:30:23, I got my site 1 operation time and site2 operation time as 9.10 hour and 4.40 hour, I would like to calculate the utilization ratio of the site 1 and site 2, them I will need to calculate the hours of the time range - totalHours=tEnd-tStart, here the tEnd is 2023-03-20 10:30:23 and tStart is 2023-03-08 10:30:23, then my site 1 utilization ratio is 9.1/totalhours100% and site 2 utilization ration is 4.4/totalhours100%.

For some of the time range we can see past 1 min, past xHr etc., for the custom Time Range, we do have to choose the Start and Stop which are tStart and tStop. here is my question how do I get the period time (hours or minutes etc) from the selected time range.

Assuming you are using nanosecond precision, I believe you can create a new column by converting the timeRangeStart and timeRangeStop timestamps to int, subtracting, and then converting from nanoseconds to hours:

from(bucket: "Bucket1")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "your-measurement")
  |> filter(fn: (r) => r["_field"] == "your-field")
  |> map(fn: (r) => ({  r with PeriodTime:  float(v: uint(v: v.timeRangeStop) - uint(v: v.timeRangeStart))/float(v:3600000000000)}))

@grant1 , Thank you so much, that is what I want and I do have _Utilization calculated out, but now I have some another issue popped out, when I switch from Table to Single Stat, I always get _value as 9.10 not _utilization 3.78% no matter how do I change the First Column setting, do you have any idea how can I only show _utilization in my single state in dashboard? also is it possible to add percentage sign % right after the number in single state?

Thank you in advance!

@grant1 I think I figured out how to show the percentage number which has to have the result in r with _value, but I am still struggle if there is any way to show % sign in the dashboard? any thought?

Hi @RichardL

Glad you got it working. I do no think InfluxDB offers any way to show the % sign. Have you used Grafana? There are many, many formatting options that you can apply if you use Grafana for visualization.

If my proposed solution solved your issue, please mark my post above as Solution so others can find it in the future.

1 Like