Support Calendar Time Operations

[adamperlin]

From ifql created by nathanielc : influxdata/ifql#201

influxdata/influxdb#2071
influxdata/influxdb#6723
influxdata/influxdb#7530
influxdata/influxdb#8183
influxdata/influxdb#3991
influxdata/influxdb#8298 (support gaps in query range)

Possibly model after SQL time funcitons https://msdn.microsoft.com/en-us/library/ms186724(v=sql.90).aspx

[nathanielc]

My thoughts on this is that we could have an intervals type. Intervals are sets of tuples, where each tuple contains a start and stop time.

There will be a few ways to define the intervals values.

• Using a duration literal - This will be interpreted as an infinite set of interval tuples. The start time of these tuples will be aligned to the time zone of the Flux script.
• Using builtin functions to produce intervals over natural calendar ranges. For example weekdays()

There will be ways to combine and manipulate these intervals.

• Multiple intervals can be unioned together, which is essentially the concat of the sets.
• Intervals and be intersected, for example intersect(intervals:[hours(start:8,stop:5), weekdays()]) would produce intervals for only 8-5 on weekdays.

Then the window function would have an intervals argument that allows providing these more complex time intervals.

Also since we are dealing with potentially infinite sets, we could use functions that return intervals instead of passing intervals around directly. That way we can optimize those functions when we execute them. Or we could implement lazy evaluation as a first class concept in Flux, but that sounds complex.

[lujop]

If #222 is implemented it will be good that this functions will be also available for select clauses.

Example use case I had:
I have a measurement with power consumption points and want to calculate hourly pricing having different hourly rates:
Ideal case will be when hours(6,12) comsumption * tarif1 when hours(12,18) consumption * tarif2 ...

[nathanielc]

@lujop Thanks for sharing your use case! I have written up a more formal specification for how we plan to do this in https://github.com/influxdata/platform/pull/448

Using a mock Flux example I believe you can accomplish that like so:

from(db:"consumptions")
    |> range(start:-24h)
    |> map(fn: (r) => {
            h = hour(time:r._time)
            if h > 6 and h <= 12 {
                return r.consumption * tarif1
            }
            if h > 12 and h <= 18 {
                return r.consumption * tarif2
            }
            return  r.consumption * tarif3
    })

[lujop]

@nathanielc Your mock looks very nice.
Is there any workaround to do this with the current flux version?

[nathanielc]

@lujop No this issue is still open, we have spec'ed out how we want it to work, but have not started on the implementation.

[aanthony1243]

replaced by epic #1551