Grouping usage time into hours of the day


#1

Hi everyone.

We use Redash to analyze logfiles of a streaming server.

We have a Clickhouse DB containing all listening operations with start time, end time and duration.

What I would like to do is this:

I want to build a bar-chart for every hour of the day (0-24) summing up the usage time that falls into that hour.

So if someone is listening from 12:30 to 14:30, 30 minutes should go into the 12h-bar, one hour into the 13h-bar and another 30 minutes into the 14h-bar.

Is this possible? When I SELECT all usage time and just GROUP it by hour of the day, the whole 2 hours go into the 12h-bar, obviously.

Please excuse me if this is a very dumb question but I am a complete newbie to this SQL-thing :confused:

Any help would be highly appreciated.