How to create time of day averages

Fluxlang
1

Hi

I whould like to create visualization where x axis is time of day and y is average of whole month

something like this

image
image880×320 22.7 KB

i did this in mysql like this:

SELECT
  CAST(
    REPLACE(
      `timestamp`,
      SUBSTRING_INDEX(`timestamp`, ' ', 1),
      CURDATE()
    ) AS DATETIME
  ) AS rounded_time,
  AVG(Powerdc1) AS 'Prosinec'
FROM
  `avg_15min`
WHERE
  `timestamp` BETWEEN '2022-12-01' AND '2023-01-01'
GROUP BY
  rounded_time

but now i want to migrate to influx and i don’t see any obvious way to do it

could anyone please help me with this query?

2

Hello @marun,
What version of InfluxDB are you using?
Also InfluxDB Cloud powered by IOx supports SQL. You might prefer to use that.

A general Flux query for finding the mo average would look like:

import "date"

from(bucket: "data")
|> range(start: 2022-12-01, stop: 2023-01-01)
|> filter(fn: (r) => r["_measurement"] == "mymeasurement" )
|> filter(fn: (r) => r["_field"] == "myfield")
|> aggregateWindow(fn: mean, every: 1mo)
|> map(fn: (r) => ({ r with hour: date.hour(r._time)}))
// specify in the UI that you want the x axis to be "hour" instead of time

And then in the UI you would customize your graph to display the hour on the x axis.

You might also be interested in the following functions:

Specifically, the date.hour(), date.second(), date.minute(), etc functions etc that returns the hour/second/minute/etc of a specified time.
Or the date.truncate() function that returns a time truncated to the specified duration unit…

3

Hi, thanks for hint

I’m using InfluxDB 2.6 local instance.

I tried your query, but it gave me error, and because I’m new to flux language i don’t know what’s wrong.

My query:

import "date"

from(bucket: "test")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "realtime")
  |> filter(fn: (r) => r["_field"] == "Powerdc1")
  |> aggregateWindow(fn: mean, every: 1mo)
  |> map(fn: (r) => ({ r with hour: date.hour(r._time)}))

error:

compilation failed: error @8:37-8:55: expected comma in property list, got DOT error @8:48-8:54: unexpected token for property key: DOT (.)

EDIT:

OK i figured it out, it needs to be |> map(fn: (r) => ({ r with hour: date.hour(t: r._time)}))

4

So i ended up doing it like this:

import "date"

from(bucket: "realtime")
  |> range(start: 2022-12-20, stop: 2023-01-01)
  |> filter(fn: (r) => r["_measurement"] == "realtime")
  |> filter(fn: (r) => r["_field"] == "Powerdc1")
  |> aggregateWindow(fn: mean, every: 15m)
  |> map(fn: (r) => ({ r with rounded_time: date.hour(t: r._time)*60 + date.minute(t: r._time)}))
  |> group(columns: ["rounded_time"])
  |> mean()
  |> map(fn: (r) => ({ r with _time: date.add(d: duration(v: r.rounded_time*60*1000000000), to: today())}))
  |> group()
  |> yield(name: "avg")

could it be improved?