Query a between time ( not datetime )

InfluxDB 2
,
1

Hi
I am new here and to flux.

I tried even with chatGPT, but nothing worked for me.

I have records that value/measurement is a SHIFT number and two tags startTime and endTime which contain hours, for example:

startTime: “06:30”
endTime: “14:45”

How do I build a query to find if now() is between those hours?

Can you help?
Thanks

2

@Michal_Augustyniak now() returns a full RFC3339 timestamp which includes day and time. Is it safe to assume that startTime and endTime refer to the current day?

Are startTime and endTime tags or fields?

3

startTime and endTIme are fileds, yes.

Is it safe to assume that startTime and endTime refer to the current day?
Yes, the goal is to find a current running shift in the factory based on the shift hours.

Example

startTime, endTime, shift
06:30, 14:00, 1
14:30, 22:00, 2

So I need to find out if time(now()) is between those hours.

4

Ok, there are a few different ways you can do this. My first thought is to use string manipulation to build time values based off the startTime and endTime strings and compare them to now():

import "strings"

data
    |> map(fn: (r) => {
        todayString = "${today()}"
        shiftStart = time(v: strings.replace(v: todayString, t: "T00:00", u: "T${r.startTime}", i: 1))
        shiftEnd = time(v: strings.replace(v: todayString, t: "T00:00", u: "T${r.endTime}", i: 1))
        
        return {r with currentShift: now() >= shiftStart and now() <= shiftEnd}
    }
5

I tried, this

import “strings”

data = from(bucket: “Azure Shift Data”)
|> range(start: 0)
|> filter(fn: (r) => r[“_measurement”] == “AG-000018” and r[“_field”] == “shiftNo”)
|> group(columns: [“_value”])
|> first()
//|> yield(name: “first”)

|> map(fn: (r) => {
todayString = “${today()}”
shiftStart = time(v: strings.replace(v: todayString, t: “T00:00:00Z”, u: “T${r.startTime}”, i: 1))
shiftEnd = time(v: strings.replace(v: todayString, t: “T00:00:00Z”, u: “T${r.endTime}”, i: 1))

return {r with currentShift: now() >= shiftStart and now() <= shiftEnd}

})

|> yield(name: “first”)

results

table, _measurement, _field, _value, _start, _stop, _time, currentShift, endTime, startTime
0 AG-000018 shiftNo 1 0 2024-04-21T21:26:34.960Z 2024-03-12T18:53:25.000Z false 14:00:00 6:00:00
1 AG-000018 shiftNo 2 0 2024-04-21T21:26:34.960Z 2024-03-12T18:53:25.000Z false 22:00:00 14:00:00
2 AG-000018 shiftNo 3 0 2024-04-21T21:26:34.960Z 2024-03-12T18:53:25.000Z false 6:00:00 22:00:00

When I try to see what shiftStart variable outputs, it returns this “2024-04-21T00:00:00.000Z”. So the replace() function is not working yet.

6

I corrected

import “strings”

data = from(bucket: “Azure Shift Data”)
|> range(start: 0)
|> filter(fn: (r) => r[“_measurement”] == “AG-000018” and r[“_field”] == “shiftNo”)
|> group(columns: [“_value”])
|> first()
//|> yield(name: “first”)

|> map(fn: (r) => {
todayString = “${today()}”
shiftStart = time(v: strings.replace(v: todayString, t: “T00:00:00.000000000Z”, u: “T${r.startTime}Z”, i: 1))
shiftEnd = time(v: strings.replace(v: todayString, t: “T00:00:00.000000000Z”, u: “T${r.endTime}Z”, i: 1))

return {r with currentShift: now() >= shiftStart and now() <= shiftEnd}

})

|> yield(name: “first”)

And it outputs this

table, _measurement, _field, _value, _start, _stop, _time, currentShift, endTime, startTime
0 AG-000018 shiftNo 1 0 2024-04-21T21:26:34.960Z 2024-03-12T18:53:25.000Z false 14:00:00 6:00:00
1 AG-000018 shiftNo 2 0 2024-04-21T21:26:34.960Z 2024-03-12T18:53:25.000Z true 22:00:00 14:00:00
2 AG-000018 shiftNo 3 0 2024-04-21T21:26:34.960Z 2024-03-12T18:53:25.000Z false 6:00:00 22:00:00

I think that is correct. I will be testing it yet

Thank you so much !!!

7

Hey, so I found a problem with the timezone.

I added

option location = timezone.location(name: “America/New_York”)

It looks like it does not change the now() function which gives incorrect results. I probably could subtract hours but this would not solve the daylight saving.

Any idea ?

8

In Flux, the location option does not update timestamps. It only adjust window boundaries across time shifts (Daylight Savings Time, British Summer Time, etc.). All timestamps stored in the _time column and returned by now() are UTC timestamps. To get your query working for your local timezone, you need to add a timezone offset to the times that get from your shift boundaries. Assuming the New York timezone:

import "strings"

data =
    from(bucket: "Azure Shift Data")
        |> range(start: 0)
        |> filter(fn: (r) => r["_measurement"] == "AG-000018" and r["_field"] == "shiftNo")
        |> group(columns: ["_value"])
        |> first()
        |> map(
            fn: (r) => {
                todayString = "${today()}"
                shiftStart = time(v: strings.replace(v: todayString, t: "T00:00:00Z", u: "T${r.startTime}:00-04:00", i: 1))
                shiftEnd = time(v: strings.replace(v: todayString, t: "T00:00:00Z", u: "T${r.endTime}:00-04:00", i: 1))

                return {r with currentShift: now() >= shiftStart and now() <= shiftEnd}
            },
        )

You are correct, this would’t solve daylight savings. This query would have to be adjusted with a new UTC offset. I’m sure there would be a way to automate this though.

1 Like
9

Updated query if someone is interested

import “strings”
import “date”
import “timezone”

option location = timezone.fixed(offset: -5h)

data = from(bucket: “Azure Shift Data”)
|> range(start: 0)
|> filter(fn: (r) => r[“_measurement”] == “AG-000018” and r[“_field”] == “shiftNo”)
|> group(columns: [“_value”])
|> sort(columns: [“_time”], desc: false)
|> last()
|> map(fn: (r) => {
todayString = “${today()}”
shiftStart = time(v: strings.replace(v: todayString, t: “T05:00:00.000000000Z”, u: “T${r.startTime}Z”, i: 1))
shiftEnd = time(v: strings.replace(v: todayString, t: “T05:00:00.000000000Z”, u: “T${r.endTime}Z”, i: 1))

return {r with currentShift: time(v: date.sub(d: 0h, from: -5h)) >= shiftStart and time(v: date.sub(d: 0h, from: -5h)) <= shiftEnd}

})
|> filter(fn: (r) => r[“currentShift”] == true)
|> keep(columns: [“_measurement”, “_value”, “endTime”, “startTime”])
|> yield(name: “last”)