Query Group by time ( Shift time)

[Karan12592]

Hi,

I want to query the database Group by time ( default shift time )

SELECT (Max(“field1”)) FROM " Measurement1" WHERE (“id” = ‘01’) AND $timeFilter GROUP BY time (Shift time) fill (null)

Example:
Shift time 1: 9:00 AM to 5 :00 pm
Shift time 2: 05:01 PM to 01:00 AM
Shift time 3: 01:01 PM to 08:59 AM

And

Shift time: 09:00 AM to 09:00 AM ( Today to Next day, 24 hrs

I want to result like this
For Shift time
01/03/2021… Shift time1 reault1
01/03/2021… Shift time2 result 2
01/03/2021… Shift time3 result3
02/03/2021… Shift time1 reault1
02/03/2021… Shift time2 result 2
02/03/2021… Shift time3 result3

Any one implemented this function.

[Anaisdg]

Hello @Karan12592,
I’m not sure that this is possible with InfluxQL However you can do this with Flux. What version of InfluxDB are you using?
There are tons of ways to manipulate time with Flux, but you’ll want. to use the timeShift() function
https://docs.influxdata.com/influxdb/v2.0/reference/flux/stdlib/built-in/transformations/timeshift/

Functions to manipulate timestamps: Time series data is difficult to work with. Flux includes several functions to work with time. However, a couple of these functions are spread out between various community package contributions and Flux packages within the documentation. A consolidated list of all of these functions provide you with a deeper understanding of how you can use Flux to work with time. The following functions allow you to manipulate your timestamps and execute a variety of time series transformations. Including:

• hourSelection(): select data between specific parts of the day.
• duration(): convert a timestamp to a duration in terms of seconds, minutes, hours, etc.
• events.Duration(): calculate the duration between events
• now(): return the current time
• system.time(): return the current time of the system
• time(): convert a Unix nanosecond timestamp to an RFC3339 timestamp
• uint(): convert RFC3339 timestamp to a Unix nanosecond timestamp
• truncateTimeColumn(): round or truncate an entire column to a specific timestamp unit
• date.truncate(): round or truncate data down to a specific timestamp unit.
• experimental.addDuration()*: add timestamps to each other
• experimental.subDuration()*: subtract timestamps from each other
• experimental.alignTime()*: compare data across a window; i.e., week over week or month over month.

[Karan12592]

Thanks for your reply.

Using version Influxdb 1.8.

I will try with Flux.