Influxdb: Feature Request: DatePart in InfluxQL

[mvadu]

Can we have InfluxQL support DatePart which is supported in almost all SQL engines? The use case is to analyze long term trend over a wide time scale, but only part of the range.

Example use cases.

1. Server utilization during business hours, we will have a data point at every 10 seconds, but we want to trend business hour vs non business hour utilization
2. Data/requests/hit volume on first of the month over time, we can always have a grafana plot the entire timescale, but that kind of overloads the analysis, I rather have a graph where I can compare 1st of every month data over last 10 years.

Proposal:
Have support for DatePart with only one argument (as we have the timescale as fixed argument), which will apply the filter on the timescale.
e.g. Select * from Host_Metrics where DatePart(hour)>8 and DatePart(hour)<17

[abrilevskiy]

+1 for such functionality. Any progress on that topic?

[sbsadsfx]

+1

[ikkerens]

+1

[ot3]

+1

[ErickSystem]

+1

[galindro]

+1

[dsvictor94]

+1

[eduardohmaciel]

+1

[gabrielzanoni]

+1

[vmaraccini]

+1

[heikobornholdt]

+1

[watchforstock]

+1

[cicciopizza]

+1

[croiuki]

+1

[swails]

+1

[hnazkani]

+1

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[mitar]

I think this is still relevant.

[mvadu]

Influx data seems to have found a nice way to decrease open issue counts drastically.. Just close all of them for which team hasn't committed to fix or provided an alternative solution in years..

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[swails]

Still relevant.

[jdubz666]

+1

[Marty]

This feature would be very helpful.

[peterpeerdeman]

+1

[secondaryObject]

+1

[michis0806]

+1

[armageddon421]

I would also like this feature very much. +1

[sanderson]

DatePart-like functionality is available in Flux using the hourSelection() function.
It likely won't be added InfluxQL.

[cruscio]

@sanderson - an hour selector addresses the first example use case, but not the second, nor the ability to '_analyze long term trend over a wide time scale, but only part of the range._'

In Flux, is it possible to use date in a filter function, to more dynamically filter time slices?

[sanderson]

@cruscio Sure, here's a quick example:

import "experimental"
import "date"

from(bucket:"example")
  // Query data from the last 12 months
  |> range(start: -12mo)
  // Filter by measurement and field
  |> filter(fn: (r) => r._measurement == "m_example" and r._field == "f_example")
  // Use the date.monthDay() function to return only data from the first of each month
  |> filter(fn: (r) => date.monthDay(t: r._time) == 1)
  // Add the month as a tag on each month
  |> map(fn: (r) => ({ r with month: date.month(t: r._time)}))
  // Add the month column to the group key so each month's first day is a separate table/series
  |> experimental.group(columns: ["month"], mode: "extend")
  // Align all the data to a starting time so you can directly compare hours of each 1st day.
  |> experimental.alignTime()

This will return data that, when visualized, looks something like this:

[martin3000]

I worked 30 years with db2 and oracle and also 10 years with mysql. I'm very surprised what problems the influxdb users have.

[teichhei]

It is really an essential feature for many applications to select records based on a time of the day, not only an absolute time stamp.
So many people having that issue and no progress... :(

[sanderson]

@teichhei What you're looking for is accomplished with hourSelection() function. This filters results to only points within hours of each day:

from(bucket:"example-bucket")
  |> range(start:-90d)
  |> filter(fn: (r) => r._measurement == "example-measurement" )
  |> hourSelection(start: 9, stop: 17)