Query Group by time ( Shift time)

Karan12592 · March 4, 2021, 5:11pm

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 · March 24, 2021, 6:22pm

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 · March 27, 2021, 5:59am

Thanks for your reply.

Using version Influxdb 1.8.

I will try with Flux.