Get start and end time of interval variable

Hi,

I have created a interval variable with values such as 8h,1d,7d and 30d. I have to group by this variable and get the count. Also i need start and end time of the interval. How can i get that?

Reference Query: SELECT count(“value”) FROM “table” WHERE (“col_name” =~ /^$MTBA/) and “value” = true and $timeFilter GROUP BY time($Time_Interval),“tag” fill(null)

Any help ?
This is little urgent.

thanks my issue has been fixed.

Sorry, but my issue has not been fixed. Can someone from Grafana team take a look?

Hi,

1- You can create a custom variable with your values:

2- Use ${__from:date:seconds} and ${__to:date:seconds} to get start and end time of the selected time range ($timaFilter).

hope it helps

How do i get it in select query?
adding like this is giving error
SELECT count(“value”),"${__from:date:seconds}" FROM “message” WHERE (“tag_name” =~ /^$MTBA_Tags2/) and “value” = true and $timeFilter GROUP BY time($Time_Interval),“tag_name” fill(null)

error: Measurements (InfluxQL): InfluxDB Error: mixing aggregate and non-aggregate queries is not supported

Usually, I use those global variables in WHERE clause or in panel labels, never in SELECT.

Otherwise, you can try this (don’t know if it has a sense):
SELECT count(“value”),last("${__from:date:seconds}") as “${__from:date:seconds}” FROM “message” WHERE (“tag_name” =~ /^$MTBA_Tags2/) and “value” = true and $timeFilter GROUP BY time($Time_Interval),“tag_name” fill(null)

This is also not giving the desired output.
Idea behind this is that we need the start and end time of the filter to get the time range. We want to implement this formula:

total_time_in_mins(from $Time_Interval)/(count+1)

if we can get value of $Time_Interval(8h,1d,7d) in the select query and then convert it into minutes, that will solve our problem

A- If you need to get total time in minutes from dashboard time range:
SELECT (${__to:date:seconds} - ${__from:date:seconds}) / (count(“value”)+1) * 60
FROM …WHERE … $timeFilter GROUP BY time($Time_Interval)

B- If you want to get it from the selected time Interval:
–B1-- create a new measurement “CustomInterval”, in your database, with 2 string fields: “Time_Interval” and “total_time_in_mins” (corresponding time in minutes)
1h, 60
8h, 480
1d, 1440
7d, 10080
–B2-- Create than a query variable “total_time_in_mins” in your dashboard:
SELECT “total_time_in_mins” FROM “CustomInterval” WHERE “Time_Interval” =~ /$Time_Interval/
–B3-- Use than the following query in your panel
SELECT $total_time_in_mins/(count(“value”)+1) FROM …WHERE … $timeFilter GROUP BY time($Time_Interval)

Thanks for the suggestion. But our database is populated automatically, so we cannot write data on our own.