Timestamp/duration mathematics

Fluxlang
1

I’m using InfluxDB 1.7.8 on Ubuntu 18.04.1 LTS which has fluxlang 0.36.2, according to the 1.7.8 release notes.

I’m trying to perform simple mathematics on my timestamps, as documented in the Duration Literals section of the Flux spec.

My Goal:
My goal is to calculate the energy supplied to some electrical loads, over the last 7 days, divided into 24 hour time windows.

Here is my query:

from(bucket: "my_bucket")
  |> range(start: -7d)
  |> filter(fn: (r) => r._measurement == "xxx_redacted_xxx" and r._field == "total_out_kwh")
  |> aggregateWindow(every: 24h, fn: mean, createEmpty: false)
  |> difference()
  |> keep(columns: ["_time", "_value"])

which returns the following:

double       dateTime:RFC3339
_value       _time
0.249629482  2019-11-08T00:00:00Z
0.25453356   2019-11-09T00:00:00Z
0.264758042  2019-11-10T00:00:00Z
0.245200554  2019-11-11T00:00:00Z
0.190323976  2019-11-12T00:00:00Z
0.218066138  2019-11-13T00:00:00Z
0.202043946  2019-11-13T09:35:09.445884868Z

What this provides me with is the total energy produced in 24h time periods for the last 7 days. The problem lies in the timestamps. If you look at the first entry in the dataset above, the timestamp is 2019-11-08T00:00:00Z and it represents the 24 hour time period up to the beginning of, but not including the 8th of November 2019. To the end user then, this is actually the energy consumed during the 7th of November 2019.

My solution:
After bit of research I found in the Flux spec that it is possible to perform mathematical operations on timestamps by adding/subtracting durations. I tried adding the following map function to the end of my query above:

  |> map(fn: (r) => ({
     _time: r._time - 1d,
     _value: r._value
  }))

which produces the following error:

Error: type error 0:0-0:0: invalid record access “_time”: duration != time

Please can anybody tell me why I’m getting this error?

Addendum: an extra prize for astute readers
Astute readers may have realised that my proposed solution works for all timestamps, except the last one. This timestamp represents the energy supplied to the electrical loads during the 13th of November. In other words:

  • from: 2019-11-13T00:00:01Z
  • to: 2019-11-13T09:35:09.445884868Z

So that means I need to know when I’m dealing with the last row in my dataset and not subtract 1d from this timestamp.

Any ideas how I can find the index and length of my dataset?

2

@roy.emmerich I think you can avoid having to map values altogether by using the timeSrc parameter on aggregateWindow(). This parameter specifies which column from windowed tables to use as the _time for the resulting aggregate record. timeSrc defaults to "_stop" which is why the timestamps of the aggregate windows represent the end of each window. If you use "_start" instead, it should give you the timestamps you’re looking for.

|> aggregateWindow(
    every: 24h,
    fn: mean,
    timeSrc: "_start",
    createEmpty: false
)

To answer your question about math operations with timestamps and durations, the Flux SPEC is a working document that doesn’t necessarily represent the current state of Flux. It is the specification engineers are building towards. Mathematic operations with time and duration values are not supported yet, but they will be soon. The Flux team is currently in the process of overhauling the Flux type system which, once complete, will allow for these types of operations. See influxdata/flux#1613.

In the meantime, there are workarounds for performing mathematical operations with timestamps: https://docs.influxdata.com/influxdb/v2.6/query-data/flux/operate-on-timestamps/ (this guide is in the flux v0.50 documentation, but everything it covers is available in Flux v0.36).

But in your particular use case, the timeSrc parameter on aggregateWindow() should solve your problem(s).

In regards to your last question, there isn’t a way (that I know of) to conditionally apply operations based on a row’s index.