Difference between 2 values ​​per day over a year in a query

[Docteur]

Hi,
How do you do in a query to loop through a date range a significant number of times?
For now, I relied on an answer from “grant1” on the topic “difference between 2 values ​​in a query”.
It works for my project except if I want to do it over 365 days (it becomes impossible to write the query).
It may be necessary to combine the aggregateWindow function with the difference function but I can’t do it
So here’s what I have write for only 2 days:

import “math”

day001period1_start = time(v: “2023-02-25T00:00:00Z”)
day001period1_stop = time(v: “2023-02-25T06:00:00Z”)
day001period2_start = time(v: “2023-02-25T06:00:00Z”)
day001period2_stop = time(v: “2023-02-25T23:59:59Z”)

day002period1_start = time(v: uint(v: day001period1_start) + uint(v:1d))
day002period1_stop = time(v: uint(v: day001period1_stop) + uint(v:1d))
day002period2_start = time(v: uint(v: day001period2_start) + uint(v:1d))
day002period2_stop = time(v: uint(v: day001period2_stop) + uint(v:1d))

day001period1_first = from(bucket: “Energie”)
|> range(start: day001period1_start, stop: day001period1_stop)
|> filter(fn: (r) => r[“_measurement”] == “TeleInfoPzem”)
|> filter(fn: (r) => r[“Equipement mesuré”] == “PzemPac”)
|> filter(fn: (r) => r[“field"] == "Puissance_totale_consommee(KWh)”)
|> first()
day001period1_last = from(bucket: “Energie”)
|> range(start: day001period1_start, stop: day001period1_stop)
|> filter(fn: (r) => r[“_measurement”] == “TeleInfoPzem”)
|> filter(fn: (r) => r[“Equipement mesuré”] == “PzemPac”)
|> filter(fn: (r) => r[“field"] == "Puissance_totale_consommee(KWh)”)
|> last()

day001period2_first = from(bucket: “Energie”)
|> range(start: day001period2_start, stop: day001period2_stop)
|> filter(fn: (r) => r[“_measurement”] == “TeleInfoPzem”)
|> filter(fn: (r) => r[“Equipement mesuré”] == “PzemPac”)
|> filter(fn: (r) => r[“field"] == "Puissance_totale_consommee(KWh)”)
|> first()
day001period2_last = from(bucket: “Energie”)
|> range(start: day001period2_start, stop: day001period2_stop)
|> filter(fn: (r) => r[“_measurement”] == “TeleInfoPzem”)
|> filter(fn: (r) => r[“Equipement mesuré”] == “PzemPac”)
|> filter(fn: (r) => r[“field"] == "Puissance_totale_consommee(KWh)”)
|> last()

day002period1_first = from(bucket: “Energie”)
|> range(start: day002period1_start, stop: day002period1_stop)
|> filter(fn: (r) => r[“_measurement”] == “TeleInfoPzem”)
|> filter(fn: (r) => r[“Equipement mesuré”] == “PzemPac”)
|> filter(fn: (r) => r[“field"] == "Puissance_totale_consommee(KWh)”)
|> first()
day002period1_last = from(bucket: “Energie”)
|> range(start: day002period1_start, stop: day002period1_stop)
|> filter(fn: (r) => r[“_measurement”] == “TeleInfoPzem”)
|> filter(fn: (r) => r[“Equipement mesuré”] == “PzemPac”)
|> filter(fn: (r) => r[“field"] == "Puissance_totale_consommee(KWh)”)
|> last()

day002period2_first = from(bucket: “Energie”)
|> range(start: day002period2_start, stop: day002period2_stop)
|> filter(fn: (r) => r[“_measurement”] == “TeleInfoPzem”)
|> filter(fn: (r) => r[“Equipement mesuré”] == “PzemPac”)
|> filter(fn: (r) => r[“field"] == "Puissance_totale_consommee(KWh)”)
|> first()
day002period2_last = from(bucket: “Energie”)
|> range(start: day002period2_start, stop: day002period2_stop)
|> filter(fn: (r) => r[“_measurement”] == “TeleInfoPzem”)
|> filter(fn: (r) => r[“Equipement mesuré”] == “PzemPac”)
|> filter(fn: (r) => r[“field"] == "Puissance_totale_consommee(KWh)”)
|> last()

union(tables: [day001period1_first, day001period1_last, day001period2_first, day001period2_last, day002period1_first, day002period1_last, day002period2_first, day002period2_last])
|> difference()
|> map(fn: (r) => ({r with _value: math.abs(x: r._value)}))

I specify that I don’t master the programming this is the reason why I try to manage with bits of code that I adapt to my situation, except that there I am blocked.
Thanks for your help if there is a solution.

[grant1]

Hello @Docteur

As I understand it, you are trying to do a union() of 8 tables and you also have some definitions about what day/period start and stop is. Have you considered using the function hourSelection()?

hourSelection() function | Flux 0.x Documentation

What do you see when your query is run? Is there any error message?

You just want the difference between the first and last values of a series over a period spanning 365 days?

[Docteur]

Hello @grant1

@grant1

What do you see when your query is run? Is there any error message?"

Sorry, an error occurred when I copy my query, here is the one that works:

import “math”

day001period1_start = time(v: “2023-02-25T00:00:00Z”)
day001period1_stop = time(v: “2023-02-25T06:00:00Z”)
day001period2_start = time(v: “2023-02-25T06:00:00Z”)
day001period2_stop = time(v: “2023-02-25T23:59:59Z”)

day002period1_start = time(v: uint(v: day001period1_start) + uint(v:1d))
day002period1_stop = time(v: uint(v: day001period1_stop) + uint(v:1d))
day002period2_start = time(v: uint(v: day001period2_start) + uint(v:1d))
day002period2_stop = time(v: uint(v: day001period2_stop) + uint(v:1d))

day001period1_first = from(bucket: “Energie”)
|> range(start: day001period1_start, stop: day001period1_stop)
|> filter(fn: (r) => r[“_measurement”] == “TeleInfoPzem”)
|> filter(fn: (r) => r[“Equipement mesuré”] == “PzemPac”)
|> filter(fn: (r) => r[“field"] == "Puissance_totale_consommee(KWh)”)
|> first()
day001period1_last = from(bucket: “Energie”)
|> range(start: day001period1_start, stop: day001period1_stop)
|> filter(fn: (r) => r[“_measurement”] == “TeleInfoPzem”)
|> filter(fn: (r) => r[“Equipement mesuré”] == “PzemPac”)
|> filter(fn: (r) => r[“field"] == "Puissance_totale_consommee(KWh)”)
|> last()

day001period2_first = from(bucket: “Energie”)
|> range(start: day001period2_start, stop: day001period2_stop)
|> filter(fn: (r) => r[“_measurement”] == “TeleInfoPzem”)
|> filter(fn: (r) => r[“Equipement mesuré”] == “PzemPac”)
|> filter(fn: (r) => r[“field"] == "Puissance_totale_consommee(KWh)”)
|> first()
day001period2_last = from(bucket: “Energie”)
|> range(start: day001period2_start, stop: day001period2_stop)
|> filter(fn: (r) => r[“_measurement”] == “TeleInfoPzem”)
|> filter(fn: (r) => r[“Equipement mesuré”] == “PzemPac”)
|> filter(fn: (r) => r[“field"] == "Puissance_totale_consommee(KWh)”)
|> last()

day002period1_first = from(bucket: “Energie”)
|> range(start: day002period1_start, stop: day002period1_stop)
|> filter(fn: (r) => r[“_measurement”] == “TeleInfoPzem”)
|> filter(fn: (r) => r[“Equipement mesuré”] == “PzemPac”)
|> filter(fn: (r) => r[“field"] == "Puissance_totale_consommee(KWh)”)
|> first()
day002period1_last = from(bucket: “Energie”)
|> range(start: day002period1_start, stop: day002period1_stop)
|> filter(fn: (r) => r[“_measurement”] == “TeleInfoPzem”)
|> filter(fn: (r) => r[“Equipement mesuré”] == “PzemPac”)
|> filter(fn: (r) => r[“field"] == "Puissance_totale_consommee(KWh)”)
|> last()

day002period2_first = from(bucket: “Energie”)
|> range(start: day002period2_start, stop: day002period2_stop)
|> filter(fn: (r) => r[“_measurement”] == “TeleInfoPzem”)
|> filter(fn: (r) => r[“Equipement mesuré”] == “PzemPac”)
|> filter(fn: (r) => r[“field"] == "Puissance_totale_consommee(KWh)”)
|> first()
day002period2_last = from(bucket: “Energie”)
|> range(start: day002period2_start, stop: day002period2_stop)
|> filter(fn: (r) => r[“_measurement”] == “TeleInfoPzem”)
|> filter(fn: (r) => r[“Equipement mesuré”] == “PzemPac”)
|> filter(fn: (r) => r[“field"] == "Puissance_totale_consommee(KWh)”)
|> last()

union(tables: [day001period1_first, day001period1_last, day001period2_first, day001period2_last, day002period1_first, day002period1_last, day002period2_first, day002period2_last])
|> difference()
|> map(fn: (r) => ({r with _value: math.abs(x: r._value)}))

The result:

For information, the result (without difference() function):

@grant1

You just want the difference between the first and last values of a series over a period spanning 365 days?

Actually I have a PZEM energy counter that increments a counter in InfluxDB2.
My energy supplier bills differently according to 2 periods during the day:
-the first period from 21:00:00Z to 05:00:00Z
-the second period from 05:00:00Z to 21:00:00Z
So my idea is to find the consumption during the 2 periods of the day (with the function difference()) and to repeat it over a year (my code works over 2 days only and it is already heavy )

@grant1

As I understand it, you are trying to do a union() of 8 tables and you also have some definitions about what day/period start and stop is. Have you considered using the function hourSelection() ?

I don’t know the function hourSelection(), I going to study if I understand how it works.
I had looked at the function aggregateWindow() to do a repetition but it does not seem to be a solution in my case (2 unequal periods per day).
I hope I was able to clarify my problem.

[grant1]

Much clearer now. Let’s forget about the two different time periods each day and for now focus on the task under the assumption that every day is 24 hours. My approach would be to create a table of the first value each day, then a second table of the last value each day, and aggregate each table by day (so you can query the full 365 days, but you will get 365 “first” values and 365 "last values).

Using my own test data (of temperature data taken throughout each day) and looking at an 8-day range in February, I was able to create this:

Start by querying the data and setting the aggregateWindow() function to aggregate every 1 day and use the “first” value each day. Use the set() function to create a tag called “first_value_of_the_day”.

First = from(bucket: "Bucket1")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "WeatherData")
  |> filter(fn: (r) => r["_field"] == "OutdoorTemp")
  |> aggregateWindow(every: 1d, fn: first)
  |> set(key: "newValue", value: "first_value_of_the_day")
  |> yield(name: "first")

and then the “last” values, using a similar approach:

Last = from(bucket: "Bucket1")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "WeatherData")
  |> filter(fn: (r) => r["_field"] == "OutdoorTemp")
  |> aggregateWindow(every: 1d, fn: last)
  |> set(key: "newValue", value: "last_value_of_the_day")
  |> yield(name: "last")

and finally, a union() function, a pivot() function (to get the first and last values displayed on the same row), and a map() function to compute the difference betweent the first value of the day and the last value of the day:

union(tables: [First, Last])
  |> pivot(rowKey:["_time"], columnKey: ["newValue"], valueColumn: "_value")
  |> map(fn: (r) => ({ r with daily_difference: r.last_value_of_the_day - r.first_value_of_the_day }))  

[Docteur]

@grant1
I just did the test with my values, here is the result of the query:

It is therefore effective if I wish to know the total daily consumption (i.e. from 00:00:00 to 23:59:59)
Note: There is however a small gap between each day example “last du 25FEV = 214.940” and “first du 26FEV = 214.950”

Unfortunately for my part, I want to know the consumption over defined periods of the day and in this case the “first” and “last” Parameters of the function ‘aggregateWindow (every: 1d, fn: first)’ or ‘aggregateWindow (every: 1d, fn: last)’ does not seem appropriate.
My problem: Find the consumption of each period1 (from 21:00Z to 05:00z) and period2 (from 05:00Z to 21:00Z) over the year.

In any case, thank you for your proposal as well as for your explanation which is very useful to me.
I keep searching.

[grant1]

@Docteur

Note: There is however a small gap between each day example “last du 25FEV = 214.940” and “first du 26FEV = 214.950”

I have that same strange behavior in my data…I think it has to do with how a day is defined, the time window selected, etc.

@Docteur

I want to know the consumption over defined periods of the day

I think the easiest for the above would be to use the hourSelection() function.

[Docteur]

That’s it, I was able to advance to the “intermediate” stage.
Now I have 4 correct tables:
Table01 => counter at start of the period1 (at 05:00Z of each day)
Table02 => counter at stop of the period1 (at 21:00Z of each day)
Table03 => counter at start of the period2 (at 21:00Z of each day)
Table04 => counter at stop of the period2 (at 05:00Z of each day)
As I couldn’t get what I wanted with the function ‘hourSelection()’, I came back to the function ‘aggregateWindow()’ with an offset value to have exactly the 2 periods per day I was looking for.

The query:

import "math"

Range_start = 2023-02-28T05:00:00Z
Range_stop = 2023-03-02T05:00:00Z

//start1 = Range_start
start2 = time(v: uint(v: Range_start) + uint(v:16h))
//stop1 = start2
stop2 = time(v: uint(v: Range_start) + uint(v:24h))

Period1Start = from(bucket: "Energie")
  |> range(start: Range_start, stop: Range_stop)
  |> filter(fn: (r) => r["_measurement"] == "TeleInfoPzem")
  |> filter(fn: (r) => r["Equipement mesuré"] == "PzemPac")
  |> filter(fn: (r) => r["_field"] == "Puissance_totale_consommee_(KWh)")
  |> aggregateWindow(every: 24h, offset: 5h, timeSrc: "_start", fn: first)
  |> set(key: "newValue", value: "first_value_of_period1")
  |> yield(name: "period1_start")

Period2Start = from(bucket: "Energie")
  |> range(start: start2, stop: Range_stop)
  |> filter(fn: (r) => r["_measurement"] == "TeleInfoPzem")
  |> filter(fn: (r) => r["Equipement mesuré"] == "PzemPac")
  |> filter(fn: (r) => r["_field"] == "Puissance_totale_consommee_(KWh)")
  |> aggregateWindow(every: 24h, offset: 21h, timeSrc: "_start", fn: first)
  |> set(key: "newValue", value: "first_value_of_period2")
  |> yield(name: "period2_start")

Period1Stop = from(bucket: "Energie")
  |> range(start: start2, stop: Range_stop)
  |> filter(fn: (r) => r["_measurement"] == "TeleInfoPzem")
  |> filter(fn: (r) => r["Equipement mesuré"] == "PzemPac")
  |> filter(fn: (r) => r["_field"] == "Puissance_totale_consommee_(KWh)")
  |> aggregateWindow(every: 24h, offset: 21h, timeSrc: "_start", fn: first)
  |> set(key: "newValue", value: "last_value_of_period1")
  |> yield(name: "period1_stop")

Period2Stop = from(bucket: "Energie")
  |> range(start: stop2, stop: time(v: uint(v: Range_stop) + uint(v:30s)))
  |> filter(fn: (r) => r["_measurement"] == "TeleInfoPzem")
  |> filter(fn: (r) => r["Equipement mesuré"] == "PzemPac")
  |> filter(fn: (r) => r["_field"] == "Puissance_totale_consommee_(KWh)")
  |> aggregateWindow(every: 24h, offset: 5h, timeSrc: "_start", fn: first)
  |> set(key: "newValue", value: "last_value_of_period2")
  |> yield(name: "period2_stop")

The result:

Now I’m stuck for the consumption calculations (difference between the start and the end of the 2 periods).

I tried this:

union(tables: [Period1Start, Period1Stop, Period2Start, Period2Stop])
|> difference()
|> map(fn: (r) => ({r with _value: math.abs(x: r._value)}))

and got this:

Inconsistent calculations

[grant1]

@Docteur

In your union, you forgot to include the pivot() function. Also, don’t use the difference() function but instead use two separate map() functions to calculate the differences.

union(tables: [Period1Start, Period1Stop, Period2Start, Period2Stop])
  |> pivot(rowKey:["_time"], columnKey: ["newValue"], valueColumn: "_value")
  |> map(fn: (r) => ({ r with daily_difference_period1: r.last_value_of_period1 - r.first_value_of_the_period1 }))
  |> map(fn: (r) => ({ r with daily_difference_period2: r.last_value_of_period2 - r.first_value_of_the_period2 }))

[Docteur]

Thanks @grant1
I couldn’t use the function pivot() as it was because I didn’t have an identical rowkey to characterize my 2 daily periods.
So I modified the query to add a “ref_period” column to my tables with the function map()

The new query:

import "math"
import "date"

Range_start = 2023-02-28T05:00:00Z
Range_stop = 2023-03-02T05:00:00Z

//start1 = Range_start
start2 = time(v: uint(v: Range_start) + uint(v:16h))
//stop1 = start2
stop2 = time(v: uint(v: Range_start) + uint(v:24h))

Period1Start = from(bucket: "Energie")
  |> range(start: Range_start, stop: Range_stop)
  |> filter(fn: (r) => r["_measurement"] == "TeleInfoPzem")
  |> filter(fn: (r) => r["Equipement mesuré"] == "PzemPac")
  |> filter(fn: (r) => r["_field"] == "Puissance_totale_consommee_(KWh)")
  |> aggregateWindow(every: 24h, offset: 5h, timeSrc: "_start", fn: first)
  |> set(key: "period_point", value: "first_value_of_period1")
  |> map(fn: (r) => ({r with ref_period: date.add(d: 0h, to:r._time)}))
  |> yield(name: "period1_start")

Period2Start = from(bucket: "Energie")
  |> range(start: start2, stop: Range_stop)
  |> filter(fn: (r) => r["_measurement"] == "TeleInfoPzem")
  |> filter(fn: (r) => r["Equipement mesuré"] == "PzemPac")
  |> filter(fn: (r) => r["_field"] == "Puissance_totale_consommee_(KWh)")
  |> aggregateWindow(every: 24h, offset: 21h, timeSrc: "_start", fn: first)
  |> set(key: "period_point", value: "first_value_of_period2")
  |> map(fn: (r) => ({r with ref_period: date.add(d: 0h, to:r._time)}))
  |> yield(name: "period2_start")

Period1Stop = from(bucket: "Energie")
  |> range(start: start2, stop: Range_stop)
  |> filter(fn: (r) => r["_measurement"] == "TeleInfoPzem")
  |> filter(fn: (r) => r["Equipement mesuré"] == "PzemPac")
  |> filter(fn: (r) => r["_field"] == "Puissance_totale_consommee_(KWh)")
  |> aggregateWindow(every: 24h, offset: 21h, timeSrc: "_start", fn: first)
  |> set(key: "period_point", value: "last_value_of_period1")
  |> map(fn: (r) => ({r with ref_period: date.sub(d: 16h, from:r._time)}))
  |> yield(name: "period1_stop")

Period2Stop = from(bucket: "Energie")
  |> range(start: stop2, stop: time(v: uint(v: Range_stop) + uint(v:30s)))
  |> filter(fn: (r) => r["_measurement"] == "TeleInfoPzem")
  |> filter(fn: (r) => r["Equipement mesuré"] == "PzemPac")
  |> filter(fn: (r) => r["_field"] == "Puissance_totale_consommee_(KWh)")
  |> aggregateWindow(every: 24h, offset: 5h, timeSrc: "_start", fn: first)
  |> set(key: "period_point", value: "last_value_of_period2")
  |> map(fn: (r) => ({r with ref_period: date.sub(d: 8h, from:r._time)}))
  |> yield(name: "period2_stop")

The result:

At this point everything seems fine to me.

By adding:

union(tables: [Period1Start, Period1Stop, Period2Start, Period2Stop])

The result:

Always good to me.

Now with:

union(tables: [Period1Start, Period1Stop, Period2Start, Period2Stop])
  |> pivot(rowKey:["ref_period"], columnKey: ["period_point"], valueColumn: "_value")

The result:

It’s not like hoping. I thought I had (before calculation)

The more I learn, the more I realize my shortcomings.

[grant1]

Hi @Docteur

What happens when you do this?
|> pivot(rowKey:["_time"], columnKey: ["period_point"], valueColumn: "_value")

It should make it so the four values (first_value_of_period1, etc.) are on the same row (see my screenshot below with 2 values).

I also noticed you had a mistake in Period1Stop and Period2Stop. The aggregateWindow function should be “last” (you have “first”).

  |> aggregateWindow(every: 24h, offset: 21h, timeSrc: "_start", fn: first)

EDIT: If the above works as planned, you will not need the “ref_period” column that you added.

[Docteur]

@grant1
Sorry, I didn’t show the result of your proposition on my query of 04/03/23 03:48am.

So when I add your proposition:

union(tables: [Period1Start, Period1Stop, Period2Start, Period2Stop])
  |> pivot(rowKey:["_time"], columnKey: ["newValue"], valueColumn: "_value")

The result was:

As I explained (in my mind), the function pivot() was enable to collect a unique value per period (with RowKey:["_time"]) to align values of “first_value_of_period1” and "last_value_of_period1 in a row, then “first_value_of_period2” and “last_value_of_period2” in an other row.
It’s the other side of the coin to have right counters at the beginning and at the end of my 2 periods of the day (05:00Z to 21:00Z and 21:00Z to 05:00Z) with my settings on function aggregateWindows() (I voluntarily played with “fn: first” for Period1Stop and Period2Stop to do that).

So ( as I have have right counters), I just added a “ref_period” column in my query of 04/03/23 06:17pm to set an unique time value per period (for good behavior of the futur function pivot() with RowKey:["ref_period"]).

Unfortunately with:

union(tables: [Period1Start, Period1Stop, Period2Start, Period2Stop])
  |> pivot(rowKey:["ref_period"], columnKey: ["period_point"], valueColumn: "_value")

The result is (as I said yesterday):

[grant1]

Hi @Docteur

Here is what works for me (again, using my weather data spanning a 4 day period). You will see that I have used the hourSelection function, and also found this to allow overnight time ranges.

FirstPeriod1 = from(bucket: "Bucket1")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> hourSelection(start: 5, stop: 21)  
  |> filter(fn: (r) => r["_measurement"] == "WeatherData")
  |> filter(fn: (r) => r["_field"] == "OutdoorTemp")
  |> aggregateWindow(every: 1d, fn: first)
  |> set(key: "newValue", value: "first_value_of_the_day_period1")
  |> yield(name: "first_period1")

 LastPeriod1 = from(bucket: "Bucket1")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> hourSelection(start: 5, stop: 21)  
  |> filter(fn: (r) => r["_measurement"] == "WeatherData")
  |> filter(fn: (r) => r["_field"] == "OutdoorTemp")
  |> aggregateWindow(every: 1d, fn: last)
  |> set(key: "newValue", value: "last_value_of_the_day_period1")
  |> yield(name: "last-period1")

Period1_Union = union(tables: [FirstPeriod1, LastPeriod1])
  |> pivot(rowKey:["_time"], columnKey: ["newValue"], valueColumn: "_value")
  |> map(fn: (r) => ({ r with period1_difference: r.last_value_of_the_day_period1 - r.first_value_of_the_day_period1 }))    
  |> yield(name: "Period1_Union")

FirstPeriod2 = from(bucket: "Bucket1")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> hourSelection(start: 21, stop: 5, timeColumn: "_time")
  |> filter(fn: (r) => r["_measurement"] == "WeatherData")
  |> filter(fn: (r) => r["_field"] == "OutdoorTemp")
  |> aggregateWindow(every: 1d, fn: first)
  |> set(key: "newValue", value: "first_value_of_the_day_period2")
  |> yield(name: "first_period2")

LastPeriod2 = from(bucket: "Bucket1")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> hourSelection(start: 21, stop: 5, timeColumn: "_time")
  |> filter(fn: (r) => r["_measurement"] == "WeatherData")
  |> filter(fn: (r) => r["_field"] == "OutdoorTemp")
  |> aggregateWindow(every: 1d, fn: last)
  |> set(key: "newValue", value: "last_value_of_the_day_period2")
  |> yield(name: "last-period2")

Period2_Union = union(tables: [FirstPeriod2, LastPeriod2])
  |> pivot(rowKey:["_time"], columnKey: ["newValue"], valueColumn: "_value")
  |> map(fn: (r) => ({ r with period2_difference: r.last_value_of_the_day_period2 - r.first_value_of_the_day_period2 }))    
  |> yield(name: "Period2_Union")

Result is two union tables:

Period1_Union

Period2_Union

If you absolutely need to have one single table, then I think you could do yet another union of Period1_Union and Period2_Union

[grant1]

Perhaps some additional helpful info…

Here is the 4-day window with hourSelection from 5 to 21:

and here is the same 4-day window with hourSelectino 21 to 5:

You can see the start date is a bit off (am sure it’s correctable with some adjustments to the start time or whatever). You can generally see the above “windows” are corresponding with what you are after.

[Docteur]

@grant1
First part of your query proposition with my Energy data:

Range_start = 2023-02-28T00:00:00Z
Range_stop = 2023-03-02T00:00:00Z

FirstPeriod1 = from(bucket: "Energie")
  |> range(start: Range_start, stop: Range_stop)
  |> hourSelection(start: 5, stop: 21)  
  |> filter(fn: (r) => r["_measurement"] == "TeleInfoPzem")
  |> filter(fn: (r) => r["Equipement mesuré"] == "PzemPac")
  |> filter(fn: (r) => r["_field"] == "Puissance_totale_consommee_(KWh)")
  |> aggregateWindow(every: 1d, fn: first)
  |> set(key: "newValue", value: "first_value_of_the_day_period1")
  |> yield(name: "first_period1")

LastPeriod1 = from(bucket: "Energie")
  |> range(start: Range_start, stop: Range_stop)
  |> hourSelection(start: 5, stop: 21)  
  |> filter(fn: (r) => r["_measurement"] == "TeleInfoPzem")
  |> filter(fn: (r) => r["Equipement mesuré"] == "PzemPac")
  |> filter(fn: (r) => r["_field"] == "Puissance_totale_consommee_(KWh)")
  |> aggregateWindow(every: 1d, fn: last)
  |> set(key: "newValue", value: "last_value_of_the_day_period1")
  |> yield(name: "last-period1")

FirstPeriod2 = from(bucket: "Energie")
  |> range(start: Range_start, stop: Range_stop)
  |> hourSelection(start: 21, stop: 5, timeColumn: "_time")
  |> filter(fn: (r) => r["_measurement"] == "TeleInfoPzem")
  |> filter(fn: (r) => r["Equipement mesuré"] == "PzemPac")
  |> filter(fn: (r) => r["_field"] == "Puissance_totale_consommee_(KWh)")
  |> aggregateWindow(every: 1d, fn: first)
  |> set(key: "newValue", value: "first_value_of_the_day_period2")
  |> yield(name: "first_period2")

LastPeriod2 = from(bucket: "Energie")
  |> range(start: Range_start, stop: Range_stop)
  |> hourSelection(start: 21, stop: 5, timeColumn: "_time")
  |> filter(fn: (r) => r["_measurement"] == "TeleInfoPzem")
  |> filter(fn: (r) => r["Equipement mesuré"] == "PzemPac")
  |> filter(fn: (r) => r["_field"] == "Puissance_totale_consommee_(KWh)")
  |> aggregateWindow(every: 1d, fn: last)
  |> set(key: "newValue", value: "last_value_of_the_day_period2")
  |> yield(name: "last-period2")

The result (wrong ):

Normally it should look like this:

The period1 without “overnight time range” (05:00Z to 21:00Z) seems to be correct but the period2 (21:00Z to 05:00Z) returns false data or rather not the expected ones.

Did I miss something with the function hourSelection()?

[grant1]

Hi @Docteur

So upon closer review of my example, I concur with you that the first and last values from the period 21h to 5h (the overnight hours) are not correct. The problem with this limit in the hourSelection function seems to be well documented (going back years), but even with the solutions proposed, I am still not getting the correct values.

If the above can be fixed, then I think the problem is solved.

More ideas:

Select data from specific hours | Additional resources | InfluxData...

[Docteur]

Hi @grant1
Thank you very much for your patience.
I have already made good progress and my query of 04/03/23 06:17pm gives the expected result before calculation.
So I will process this part with MS Excel from CSV.
Thanks again, have a nice day.

[grant1]

Thanks @Docteur

This subject of querying a time span that goes across more than one day (i.e. 23:59:59) comes up from time to time, and I am curious to find a solution. If I do, I will post here. In the meantime, I believe the query developed above will get you closer to where you want to go.

[Patrick808]

Personally, I think the best solution is to look at it as three windows-- early morning, daytime, and evening/night, and you add the early morning and evening data together separately. That way you still have clean information on a daily basis.

…but it isn’t as elegant as a single hour_select.

[Docteur]

Hi @Patrick808
Thank you for your remark.
Indeed, since the transition to the next day is a problem, splitting this period in two can work.
However in my case, this should not improve the result of my query of 04/03/23 06:17pm.
I’ll give it a try anyway.