Query specific time range and day

Hello,

I want to query influx for times series with :

  • Start time at 08h00
  • end time at 19:00
    -and for a range day : Monday to Friday

Is there a way to do it ?

Regardds

Hello @Nibeckj,
Welcome.
You can create variables for those times and use the flux date package.
https://v2.docs.influxdata.com/v2.0/visualize-data/variables/create-variable/
https://v2.docs.influxdata.com/v2.0/reference/flux/stdlib/date/

Hello,

I’m struggling with the same problem: I want to show all data with date = current day and time 00:00 to 06:00. As I’m new to Flux I can’t figure out how to create a variable with a dynamic date and a fixed time.

In python or (other languages) I would concatenate a date string and then transform it to a date (or I would create a date and then add the hours/minutes).
In Flux I tried without any success: my_start_time = date.year(t: now()) date.month(t: now()) date.day(t: now()) (06:00:00Z)

Surely there is a simple solution … I only can’t find it (Google is not my friend today :frowning_face:).

@Anaisdg: Can you please give another hint how to combine create-variable and the date-packages?
@Nibeckj: Did you find a solution to your problem

@bjoern_b There are a few Flux functions that will help you do this:

For @Nibeckj original use case, the query would look something like:

import "date"

from(bucket: "example-bucket")
  |> range(start: -90d)
  |> filter(fn: (r) => r._measurement == "example-measurement")
  |> filter(fn: (r) =>
    date.weekDay(t: r._time) >= 1 and
    date.weekDay(t: r._time) <= 5
  )
  |> hourSelection(
    start: 8,
    stop: 19,
  )

For your use case, it would look something like:

import "date"

today = date.truncate(t: now(), unit: 1d)

from(bucket: "example-bucket")
  |> range(start: -90d)
  |> filter(fn: (r) => r._measurement == "example-measurement")
  |> filter(fn: (r) => date.truncate(t: r._time, unit: 1d) == today)
  |> hourSelection(start: 0, stop: 6)
1 Like

@scott That’s so great. Thanks a lot for the quick help! Such an easy solution. :+1:

Just for notice: For my final solution I combined it with a union() so that I can get a result set which spans over midnight (22:00 the day before until 06:00 the current day). Perhaps there is a smarter solution but for me it is working. :smiley:

import "date"

yesterday = date.truncate(t: -1d, unit: 1d)
bucket_yesterday = from(bucket: "telegraf")
  |> range(start: -3d)
  |> filter(fn: (r) => r["_measurement"] == "dBA")
  |> filter(fn: (r) => r["type"] == "output_to")
  |> filter(fn: (r) => r["_field"] == "value")
  |> filter(fn: (r) => date.truncate(t: r._time, unit: 1d) == yesterday)
  |> hourSelection(start: 22, stop: 23)
  |> aggregateWindow(every: 1s, fn: mean, createEmpty: false)

today = date.truncate(t: now(), unit: 1d)
bucket_today = from(bucket: "telegraf")
  |> range(start: -3d)
  |> filter(fn: (r) => r["_measurement"] == "dBA")
  |> filter(fn: (r) => r["type"] == "output_to")
  |> filter(fn: (r) => r["_field"] == "value")
  |> filter(fn: (r) => date.truncate(t: r._time, unit: 1d) == today)
  |> hourSelection(start: 0, stop: 5)
  |> aggregateWindow(every: 1s, fn: mean, createEmpty: false)

union(tables: [bucket_yesterday, bucket_today])
  |> yield(name: "mean")

@scott I just stumbled on a “bonus question” regarding hourSelection():

I’m live Germany. So my timezone is currently UTC+1 and in summer it’s UTC+2. hourSelection() uses (at least in my Data Explorer) UTC.
That means, if I want 22 to 6 o’clock local time:

  • Currently, during winter time, I have to set hourSelection(start: 21, stop: 23) and hourSelection(start: 0, stop: 5)
  • During summer time I would have to change the query to hourSelection(start: 20, stop: 23) and hourSelection(start: 0, stop: 4)

Is there any possibility to dynamize this behaviour so that hourSelection() uses my locale time (zone) or that I can calculate the start/stop time dynamically based on additional informations (variables, etc.)?

@bjoern_b It’s not a super elegant solution, but it should work. If you know the dates and time that the time changes, you could conditionally set variables:

import "date"

daylightSavingsStart = 2021-02-28T04:00:00Z
daylightSavingsEnd = 2021-010-31T05:00:00Z
today = date.truncate(t: now(), unit: 1d)
yesterday = date.truncate(t: -1d, unit: 1d)

yesterdayHourStart = if today > daylightSavingsStart and today < daylightSavingsEnd then 20 else 21
todayHourStop = if today > daylightSavingsStart and today < daylightSavingsEnd then 4 else 5

bucket_yesterday = from(bucket: "telegraf")
  |> range(start: -3d)
  |> filter(fn: (r) => r["_measurement"] == "dBA")
  |> filter(fn: (r) => r["type"] == "output_to")
  |> filter(fn: (r) => r["_field"] == "value")
  |> filter(fn: (r) => date.truncate(t: r._time, unit: 1d) == yesterday)
  |> hourSelection(start: yesterdayHourStart, stop: 23)
  |> aggregateWindow(every: 1s, fn: mean, createEmpty: false)

bucket_today = from(bucket: "telegraf")
  |> range(start: -3d)
  |> filter(fn: (r) => r["_measurement"] == "dBA")
  |> filter(fn: (r) => r["type"] == "output_to")
  |> filter(fn: (r) => r["_field"] == "value")
  |> filter(fn: (r) => date.truncate(t: r._time, unit: 1d) == today)
  |> hourSelection(start: 0, stop: tordayHourStop)
  |> aggregateWindow(every: 1s, fn: mean, createEmpty: false)

union(tables: [bucket_yesterday, bucket_today])
  |> yield(name: "mean")