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")
1 Like

Hello,

I was wondering if it is possible to use these queries with the python client.

In order to try your solutions, in python I tried:

from influxdb_client import InfluxDBClient
client = InfluxDBClient(url=url, token=token, org=org)
query_api = client.query_api()

query_api.query(org=org, query = 'import "date"')

But this doesn’t work since it’s a bad request (ApiException: 400) because the Flux script returns no streaming data.

Is there any possibility to do these queries on a specific time range and day with the python client?

Hi,
I’ve seen and used the:

|> hourSelection(start: 9, stop: 14)

Which is nice, but I need to filter data from, say 09:00 to 14:30. What would be my best option?

|> hourSelection(start: 9, stop: 14)
|> filter(fn: (r) => 
    if date.minute(t: r._time) >= 30 then
        if date.hour(t: r._time) > 14 then
            false
        else
        true      
      else
      true
  )

I’ve tried the above, but I can’t seem to chain the date.hour and date.minute together. They both work separately, but not together. I’m probably missing something very basic.

Thanks!
Micke

Solution to my own question: The above works in the latest version 2.5.1. Didn’t work properly in the last image I was running, I think that was 2.4.0

Still open for cleaner suggestions though, if anyone has one. :slight_smile: