Time range in grafana

Grafana
,
1

Hey i was trying to filter data using time range i am using json api as my data source. What i am trying to do is, in my data source there are columns named discharge, water and date so i want to filter discharge and water using date how can i do that

1 Like
2

When you define the query that connects to your data source (json API), check the second tab “transform” and then add a kind of transform that acts as a filter and do whatever you need.

Usually, to do filtering operations, you’re better off using an underlying query language if you can (like SQL) to do the filtering before the data gets to grafana, but if it’s a JSON API, sometimes the API won’t let you do that, so you have to do the filtering after the fact in grafana.

Special note - watch out for data types. If your API is returning text strings (which it probably is) you will likely need to first add a transform that turns the text string into a date (another column of data), so that you can filter on a date, and not a string.

image
image1652Ă—814 45.9 KB

Transform docs can be found here

2 Likes
3

thank you for your answer sir but what i am trying to do use discharge for x-axis and water for y-axis and select the time range on the time selection located on the top of the dashboard is that possible

4

Please post sample json from your api modifying any sensitive data. like this inline

{
 "discharge": 12,
 "water": 33.3,
"date ": "2023-10-19 12:00:00"

}

Also include long, lat as it seems you want to plot it on geomap

5

{
“tele_Data_ID”: “3d5f6efe-e52f-4b4e-8031-048e5e776b3a”,
“lS_ID”: “fb2053ce-f1c8-4e8f-8b41-0101bb502e93”,
“date_Of_Measurement”: “2021-11-23T00:00:00”,
“time_Of_Measurement”: “05:09:30”,
“water_Level”: 45.4,
“discharge”: 4560,
“remarks”: “”,
“x_Coordinate”: “”,
“y_Coordinate”: “”,
“device_ID”: “1324090e-794e-2474-311a-0fe2ccd2c320”,
“sediment_PPM”: null,
“sediment_Min_PPM”: null,
“sediment_Max_PPM”: null,
“temperature”: null,
“eConductivity”: null
},
this is what my api returns so i want to put water_Level and discharge on x and y axis and filter the data by date_Of_Measurement

1 Like
6

Thanks for sharing the API response JSON body.

One suggestion about your API design, before diving into Grafana. I’d be tempted to combine date_Of_Measurement and time_Of_Measurement into a single datetime_Of_Measurement field. That way you’ll be able to filter not only on Date, but also on Time :tada:

Also one more question for you:

  • Which JSON datasource are you using?
    I like to use the Infinity Datasource, as I find it more flexible/powerful.

So picking up where @davidallen5 left off:

I got this working as follows:

  • First, enable the (currently experimental) feature-toggle transformationsVariableSupport. See Configure feature toggles | Grafana documentation

  • Duplicate the time-column, but convert it into a Unix timestamp, using the “Add field from calculation” transform. I happened to use “Reduce row” with “Total” (see screenshot), but other combinations can work here too. Give the field a new name, maybe timestamp

  • Then add another transform “Filter data by values”. This time choose the new field you just named, and then choose Is between and the values $__from and $__to. This will filter your data down to only that which falls inside the range chosen on the dashboard’s time-range picker

Screenshot 2023-11-21 at 15.50.03
Screenshot 2023-11-21 at 15.50.03773Ă—900 54.1 KB

1 Like
7

so it is not for geomap?

8

Sorry for reviving a months-old thread, but I’m trying to do something similar with a table coming from SQL Server. Grafana correctly recognizes my column as a time (the column header, when the Table View switch is turned on, shows the little clock icon next to it, with the tooltip indicating “time”).

I’m trying to allow the user to specify the time range using the dropdown at the top of the screen to filter the rows in my table visual. I’ve added the “Filter data by values” transform, with Filter Type set to Include, Conditions set to “Match All” (I’ve also tried “Match Any”), Field to my date/time field, but the Match dropdown only includes:

Is null
Is not null
Is equal
Is not equal

…unlike “samjewell”'s screenshot above, which clearly shows “is between” (and “value” set to “$__from” and “$__to”, which is exactly what (I believe) is exactly what I need to do.

9

use the filter values in your query itself and avoid transforms

select * 
from table_name 
where $__timeFilter(DateTime) 
order by DateTime DESC;
10

I was keeping that option in my back pocket, so if that’s the solution, then so be it. I would’ve liked to avoid revisiting all my queries for all my dashboards.

Just out of curiosity, should a simple table with a date/time value NOT automatically respect the date range selection at the top of the screen? I haven’t seen any documentation that suggests it should not work.

Also, I would’ve expected to be able to hit the SQL DB once (with no time range specified), and the time selection would be responsible for filtering out whatever falls outside the range - without re-running the query. But if the time range is part of the SQL query itself, then that means the DB will be hit again, no?

Not that this is a huge concern - I know I’m never gonna get a huge dataset back - but I was hoping to avoid re-hitting the DB if there was a more efficient way to do that.

(meanwhile, your where clause with the variable is working great - thanks!)

1 Like
11

and the next person will complain “why does it automatically respect the date ranges??”

slippery slope, and not the job of grafana. grafana is a visualization tool primarily, how you fetch your data is yours