Query specific time range and day

Nibeckj · May 25, 2020, 3:01pm

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

Anaisdg · May 26, 2020, 3:51pm

Hello @Nibeckj,
Welcome.
You can create variables for those times and use the flux date package.

bjoern_b · March 11, 2021, 8:43pm

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 ).

@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

scott · March 11, 2021, 10:15pm

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

Use hoursSelection() to filter by hour of day.
Use date.weekDay() to process and filter points by day of the week.
Use date.truncate() to truncate timestamps to a specified unit of time.

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)

bjoern_b · March 11, 2021, 11:40pm

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

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.

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

bjoern_b · March 12, 2021, 12:20am

@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.)?

scott · March 12, 2021, 5:52pm

@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")

DavidGaGySro · November 9, 2022, 3:14pm

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?

mike · November 13, 2022, 10:28pm

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

mike · November 14, 2022, 10:46pm

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.

Tuong · March 1, 2023, 10:34am

I am using version 1.8 and UTC+7.
I hope it is useful to you.

import "strings"
import "experimental"

today = time(v: "${strings.substring(v: string(v: experimental.subDuration(d: 17h, from: now())), start: 0, end: 10)}T17:00:00Z" )

from(bucket: "dokiem_hatang") 
  |> range(start: today )
  |> filter(fn: (r) => r._measurement == "dokiem_congsuat" and r._field == "congsuat_chia" )
  |> aggregateWindow(every: 5m, fn: mean)
  |> yield(name: "min")

Maarten · September 22, 2023, 4:07pm

Hallo Nibeckj,

Hello
i am a beginner on using influxdb. I saw your message while looking for a solution for the working days. I can’t get any further with the given answer. Can you help me get started?

Beste Regard, Maarten

ragman1976 · March 6, 2024, 6:29am

Hi,

I also trying to sum up data (energy consumption) between a time range using th hourSelection function.
But the code that Im using sum up the whole 24 hours. Any suggetions whats wrong with the code?

import “timezone”
option location = timezone.location(name: “Europe/Berlin”)

from(bucket: “HASS”)
|> range(start: -7d)
|> filter(fn: (r) => r[“_measurement”] == “Wh”)
|> filter(fn: (r) => r[“_field”] == “value”)
|> filter(fn: (r) => r[“domain”] == “sensor”)
|> filter(fn: (r) => r[“entity_id”] == “mqtt_haushaltsstrom_1_8_0”)
|> filter(fn: (r) => r[“friendly_name”] == “mqtt_haushaltsstrom_1_8_0”)
|> filter(fn: (r) => r[“source”] == “HA”)
|> hourSelection(start: 22, stop: 23)
|> difference()
|> aggregateWindow(every: 1d, fn: sum, timeSrc: “_start”)

Topic		Replies	Views
Dynamically Selecting Time Ranges in InfluxDB Flux Queries: Error and Solutions Dashboards influxdb , time-series , time , query , flux	2	201	April 3, 2024
Query data by week InfluxDB 2 flux	4	707	July 12, 2021
I want to retrieve data from a specific period of time InfluxDB 2	4	470	December 13, 2023
Query Time date range but a time window with in that range like 9-17 influxdb , time	0	1147	September 18, 2017
Query for current month in flux Welcome & Getting Started	6	9888	April 7, 2022

Query specific time range and day

Related Topics