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).
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.
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?
@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?
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)
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.