Pick data based on _time as dashboard variable

1

Hello!
Im trying to create a dashboard variable which fetches the _time values so that i can filter using _time in my dashboard

import “influxdata/influxdb/schema”
schema.tagValues(bucket: “message-rate”, tag: “_time”)

this is the query im using but this doesn’t work…

it’d be great if you can help me out :slight_smile:

2

@jeyakumar _time isn’t a tag, so you can’t query it using schema.tagValues. You’ll have to use a regular from() |> range() |> filter() query to do this. You can then use group() to group all rows into a single table and then distinct() to get all the distinct time values:

from(bucket: "message-rate")
    |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
    |> filter(fn: (r) => r._measurement == "example-measurement")
    |> group()
    |> distinct(column: "_time")

Depending on the nature of your data and the time range queried, this could return MANY timestamps. Know that Grafana limits variable values to 1000, so if this returns over 1000 distinct timestamps, you won’t be able to select all of them.

If you can get this variable to work, using it gets a little tricky, for a few reasons:

  • Each from() query that queries InfluxDB requires a range() to define a time range to query. You’re probably going to want to dynamically set time range based on the variable value.
  • To query a specific timestamp, you’d have to filter on _time. When using time in a filter predicate, the two timestamps have to match down to the nanosecond. If they don’t, you won’t get any results.

The query would look something like this:

// Convert the Grafana variable to a time value
timeVariable = time(v: "${timeVariable}")
// Dynamically set query range based on Grafana variable
startTime = date.add(d: -1s, to: timeVariable)
stopTime = date.add(d: 1s, to: timeVariable)

from(bucket: "message-rate")
    |> range(start: startTime, stop: stopTime)
    |> filter(fn: (r) => r._time == timeVariable)

This approach is definitely an edge case, but this is how you’d do it.

1 Like
3

Oh Thanks
Actually, its an influxdb dashboard variable
I Tried with the normal from(), range(), filter() query, but the variable doesn’t seem to work
the group() and distinct() doesn’t work as i had both int and float values which made a conflict

 runtime error @7:6-7:31: distinct: schema collision: cannot group float and integer types together

is there any other way by which i can filter data using _time and use it as a dashboard variable ?

context: im new to InfluxDB

1 Like
4

If you’re looking to define a time range, that is prepackaged in the dashboard. You set the time range with the time range selector:

image
image1792×1012 81.7 KB

It’s the button with the clock icon in the upper right corner of your dashboard. If you scroll up in that dropdown menu, there’s a “Custom Time Range” option. Click that.

To use those time selections in your query, you use the v.timeRangeStart and v.timeRangeStop in your range() function:

from(bucket: "example-bucket")
    |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
    // ...

You can learn more about the predefined dashboard variables here: Use and manage dashboard variables | InfluxDB OSS v2 Documentation

From your original question, I was thinking that you wanted to filter by a specific timestamp and only return values with that specific timestamp. If that’s the case, then let’s go back to what we were trying before…

For this specific use case, you can just cast all the values to strings since everything can cast to a string and you’re not going to use the values anyway:

from(bucket: "message-rate")
    |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
    |> filter(fn: (r) => r._measurement == "example-measurement")
    |> toString()
    |> group()
    |> distinct(column: "_time")
1 Like
5

Thanks scott…

Actually, as im not much experienced with flux querying, i went with an easier approach
I added a new tag in the bucket denoting the timestamp and used that tag as a dashboard variable, now i can pick data on a specific timestamp…

Thanks!!!

1 Like
6

This is not a good long term solution and will definitely cause problems down the road. Storing a timestamp as a tag is going to quickly result in high series cardinality which will hurt and even hinder the overall performance of your InfluxDB instance. If you’re using InfluxDB Cloud, you’re going to hit your series cardinality limit quickly.

What is your use case for having to select a specific timestamp?

1 Like
7

Oh
Yes it is pretty slow in showing up the graphs when i switch values of dashboard variables.
I’ll think about that…

The use case is that we have load tests that run everyday, and to distinguish between the same test cases with same parameters that ran on different days, we use a unique tag that denotes the date to pick a specific load test result

8

Ok, so you aren’t necessarily adding nanosecond timestamps as a tag. What precision are the dates? By day? If so, you should be ok.

1 Like
9

Yes, they are as datetime timestamp, not is seconds/nanoseconds precision
Actually what i am doing is that i formatted the date out of the timestamp and added that date as a tag while uploading