How many values are in certain time range


#1

Hi,

Sorry in advance for such a lame question but I’m having trouble with creating a report that would show time ranges in each row in first column, for example 1st row - 0-1 hour, 2nd row - 1-2 hours, 3rd row - 2-3 hours and so on. The second column would present the number of devices that are within specific time range, for example:

Time range Devices count
0 - 1 h 100
1 - 2 h 68
2 - 3 h 10

The main goal is to find out how many devices are operating within certain range of time. I know it may be hard not knowing the format of my database but maybe you would provide me at least some clue how to do this. I assume it may be achieved by using SQL native query but I couldn’t find anything on the net that would cover my needs (or did poor searching).

If it helps, my database is based on postgreSQL.


#2

You should be able to use Metabase’s binning functionality.
This is from the sample data:


I’m not sure if there’s a better way to set the bin ranges though. It seems a bit erratic.


#3

I don’t seem to find this binning functionality under “grouped by” option. Is it something that can be turned on by Admin?


#4

It seems to just turn it on when relevant.
I think it’s when you group by a numeric.


#5

The data which I’m interested doesn’t have this function
image


#6

What type is the duration field? If it’s Interval, than sadly you’ll have to wait for https://github.com/metabase/metabase/issues/9500 or alternatively do the binning (or at least extraction/conversion to hours) manually using SQL.


#7

The format of duration looks like this:
image


#8

As a string or is that just how it’s displayed. I hate to try and separate a number from that.


#9

That’s a Posgres Interval type. You can use EXTRACT to convert it to hours: https://stackoverflow.com/questions/952493/how-do-i-convert-an-interval-into-a-number-of-hours-with-postgres


#10

Thank you for all your replies. Could you be so kind and tell me how to add this “SELECT EXTRACT…” query to the following in order to convert duration into hours?

Sorry, I’m rookie in SQL queries.


#11

Try:
select extract(epoch FROM "public"."reports_eventstatistic"."duration")/3600 as "duration" from ...


#12

@sbelak thank you. This time I started with building a query with native query not the query builder:

select
  case when time_range>= 0 and time_range<= 1    then '  0 - 1 hour'
       when time_range> 1 and time_range<= 2   then ' 1 - 2 hours'
       when time_range> 2 and time_range<= 3  then ' 2 - 3 hours'
       else '3+ hours'
  end HoursRange
  from(select EXTRACT(EPOCH FROM duration)::int/3600 as time_range, duration from reports_eventstatistic order by time_range desc) HoursRange

But what I want to achieve is to add the “Devices count” column and show the data from last 7 days.
Could you please fix my query?


#13

I suggest doing it in 2 steps:

  1. create a native question

select *, EXTRACT(EPOCH FROM duration)::int/3600 as duration from reports_eventstatistic

(this will just pull raw data and add a column “duration” with hours)

  1. use the question from 1) as source for your subsequent questions. This way you can use the query builder for your actual questions rather than having to do everything in SQL.

#14

@sbelak I will definitely try doing so. Thank you for the tip.