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
Any help would be highly appreciated.