How to do a binning query in LiveQL

Question: I have a table with 2 columns (starttime, endtime). Is there a way to aggregate the number of rows for the difference between the endtime and starttime? For example, for how many rows is the difference is < 1 hour, how many between 1 hour and 2 hours, and how many > 2 hours. I would like to see the results on a single LiveView Web card.

Answer: As of Live Datamart 2.2.2, you can use a group by expression to bin by things like Hour. As a simple example:

 select count() as Count, HourBin from MyTable group by get_hour(transactionTime) as HourBin

This query will create a result set that one row per hour, where all rows are counted.

You can use WHEN clause to restrict what rows you look at.

Question: How can I bin in 3 groups? < 1 hour, <1 and < 2 and < 2?

Answer: So, as a first cut, and assuming endtime and starttime are timestamp data types:

select count() as MyCount, Buckets from MyTable
  group by (if ((endtime - starttime)/(60*60) > hours(2))
                then "old" 
                else if ((endtime - starttime)/(60*60) < hours(1))
                        then "new"
                        else "middling")
            as Buckets

You should get three rows: "new", "middling", and "old".

You can use a WHERE clause to further restrict the rows you look at.

Question: Is there any other way to do this? Perhaps I don't have LDM 2.2.2 yet.

Answer: Sure! Try something like:

select countif(int(to_seconds(endtime-starttime)/3600)<1) as low,
       countif(int(to_seconds(endtime-starttime)/3600)<2)
         - countif(int(to_seconds(endtime-starttime)/3600)<1) as mid,
       countif(int(to_seconds(endtime-starttime)/3600)>2) as high
       from eventtimes

This will give you the results in one row, sort of rotated 90 degrees.