Show last week's data as now (or: Offset v.timeRangeStart/Stop)

[ssuchter]

Hi -

I’m interested in getting data from a UI-driven time range (e.g. using v.timeRangeStart and v.timeRangeStop) and comparing it to data from 1 week before the same time range. If I could generate two tables, I can do all of the comparisons with join/math, or simply visualize both.

I can definitely take last week’s data and timeShift(duration: 7d) and turn it into this week’s timestamps.

But what I can’t figure out how to do is to do math on timeRangeStart/timeRangeStop. I was hoping I could do something like this:

old_data = from(bucket: "main")
  |> range(start: experimental.subDuration(d:7d, from:v.timeRangeStart), stop: experimental.subDuration(d:7d, from:v.timeRangeStop))
  |> timeShift(duration: 7d)
...

But I get this error:

type error 9:19-9:72: time != duration

Any ideas on how to accomplish what I’m trying to do?

Thanks, Sean

[scott]

@ssuchter You can use the experimental.alignTime() function to align the starting time of all output tables.

import "experimental"

t1 = from(bucket: "example-bucket") |> range(start: -7d, stop: -6d) |> filter(...)
t2 = from(bucket: "example-bucket") |> range(start: -1d) |> filter(...)

union(tables: [t1, t2])
  |> experimental.alignTime(alignTo: experimental.subDuration(d: 1d, from: now())

[ssuchter]

Thanks, that is helpful!

The part I’m trying to address is what’s in the range() function. In your example, you have range(start: -7d, stop: -6d). What I’d like to do is to make it relative to v.timeRangeStart so I can still use the time filter selectors in the dashboard.

I figured out something this AM. Using experimental.subDuration works when v.timeRangeStart resolves to an absolute time (e.g. ‘Custom Time Range’ in the picker), but not when it is specified as a duration (e.g. ‘Past 24h’)

It seems like the type of the v.timeRangeStart argument changes based on whether it’s a absolute or relative time, and there is logic built into the range() function to resolve this. Is there a way I can make experimental.subDuration(d: 7d, from:v.timeRangeStart) work when v.timeRangeStart is in relative times?

[scott]

Let me think on it and get back to you.

[ssuchter]

I was trying to make a crazy workaround where I did one query (for recent data) using v.timeRangeStart/v.timeRangeStop, and then extract the value from the _start and _stop columns, but I couldn’t understand how to extract them and use them in the range() operator of the next query. I was trying to follow Extract scalar values in Flux | InfluxDB OSS 2.0 Documentation, but I couldn’t get anything to work.

[scott]

@ssuchter I think I’ve come up with something that will work:

import "regexp"
import "experimental"

normalizeTime = (t) => {
  normalized =
    if regexp.matchRegexpString(r: /[n|u|s|m|h|d|w|o|y]/, v: t) then experimental.addDuration(d: duration(v: t), to: now())
    else time(v: t)
  return normalized
}

historicalPeriod = -1w
historicalStart = experimental.addDuration(d: historicalPeriod, to: normalizeTime(t: string(v: v.timeRangeStart)))
historicalStop = experimental.addDuration(d: historicalPeriod, to: normalizeTime(t: string(v: v.timeRangeStop)))

historical = from(bucket: "example-bucket")
  |> range(start: historicalStart, stop: historicalStop)
  |> filter(...)
current = from(bucket: "example-bucket")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(...)

union(tables: [historical, current])
  |> experimental.alignTime(alignTo: normalizeTime(t: string(v: v.timeRangeStart)))

[ssuchter]

Excellent, that worked.

I couldn’t get the alignTime to work, but I got it to work with timeShift(). I also found that I needed to add a new column to the historical and current and add it to the group key. So my working example looks like:

import "regexp"
import "experimental"

normalizeTime = (t) => {
  normalized =
    if regexp.matchRegexpString(r: /[n|u|s|m|h|d|w|o|y]/, v: t) then experimental.addDuration(d: duration(v: t), to: now())
    else time(v: t)
  return normalized
}

historicalPeriod = 1w
historicalStart = experimental.subDuration(d: historicalPeriod, from: normalizeTime(t: string(v: v.timeRangeStart)))
historicalStop = experimental.subDuration(d: historicalPeriod, from: normalizeTime(t: string(v: v.timeRangeStop)))

new_data = from(bucket: "main")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(...)
  |> map(fn: (r) => ({
    r with age: "recent"
  }))
old_data = from(bucket: "main")
  |> range(start: historicalStart, stop: historicalStop)
  |> timeShift(duration: historicalPeriod)
  |> filter(...)
  |> map(fn: (r) => ({
    r with age: "last_week"
  }))

union(tables: [old_data, new_data])
  |> group(columns: ["age", "host", "url"])

[scott]

Awesome, as long is it gets you to where you want to be