You can do something like this - all of the code below is available on the fiddle here. The solution makes use of the PostgreSQL range types - an extremely powerful tool, especially for this sort of work. I also use a materialised calendar table, generated as explained here.
CREATE TABLE test
(
user_id INTEGER NOT NULL,
session_id INTEGER NOT NULL,
s_start TIMESTAMP NOT NULL,
s_end TIMESTAMP NOT NULL
);
and then populate it:
INSERT INTO test VALUES
(1, 1, '2021-02-25 10:10:00.000 +0100', '2021-02-25 10:20:00.000 +0100'),
(1, 2, '2021-02-25 10:50:00.000 +0100', '2021-02-25 10:55:00.000 +0100'),
(1, 3, '2021-02-25 11:40:00.000 +0100', '2021-02-25 12:30:00.000 +0100'),
(1, 4, '2021-02-26 11:46:00 +0100', '2021-02-26 11:57:00 +0100');
-- added for testing - not 4 minutes and 7 minutes.
I added the last record for testing - the time slot's boundaries don't match the 10 minutes of the calendar table.
Now, you have to have a calendar table - this is to JOIN with your time slots and perform calculations.
CREATE TABLE calendar_range
(
dr TSTZRANGE NOT NULL,
minute_slot SMALLINT NOT NULL,
hour_slot SMALLINT NOT NULL
);
Now, this takes 50 bytes per record, so for ~ 100MB, you'll have > 30 years of records - or you may care to generate it dynamically as per the other answer - your storage, CPU and RAM will tell you what to do here - I would suggest that a permanent calendar table is the better solution, especially if you are doing this sort of calculation regularly! It will also be more performant.
I've left some of my first queries on the fiddle - this is the final one:
INSERT INTO calendar_range (dr, minute_slot, hour_slot)
SELECT
TSTZRANGE
(
'2021-01-01 00:00:00'::TIMESTAMPTZ
+ (m || ' MINUTE')::INTERVAL,
'2021-01-01 00:10:00'::TIMESTAMPTZ
+ (m || ' MINUTE')::INTERVAL,
'[)'
) AS tsr,
(m % 60)/10 AS slot,
DATE_PART
(
'HOUR',
'2021-01-01 00:00:00'::TIMESTAMPTZ
+ (m || ' MINUTE')::INTERVAL
) AS t_hour
FROM
GENERATE_SERIES(0, 100000, 10) AS t(m);
Just to check our calendar_range table:
SELECT * FROM calendar_range;
Result:
dr minute_slot hour_slot
["2021-01-01 00:00:00+00","2021-01-01 00:10:00+00") 0 0
["2021-01-01 00:10:00+00","2021-01-01 00:20:00+00") 1 0
["2021-01-01 00:20:00+00","2021-01-01 00:30:00+00") 2 0
...
... snipped for brevity
...
So, we have a range (inclusive at the start, exclusive at the end boundary), starting in 2021 and continuing for 100 days - enough to cover the sample data from the question.
And then we do a SELECT and JOIN the calendar table to the actual timeslot data as follows:
SELECT
t.user_id, t.session_id,
t.s_start::TIME, t.s_end::TIME,
CASE
WHEN (LOWER(cr.dr) >= t.s_start) AND (UPPER(cr.dr) <= t.s_end) THEN 10
ELSE
CASE
WHEN (LOWER(cr.dr) < t.s_start) AND (UPPER(cr.dr) <= t.s_end) THEN
EXTRACT(EPOCH FROM (UPPER(cr.dr) - t.s_start)) / 60
ELSE EXTRACT(EPOCH FROM (t.s_end - LOWER(cr.dr))) / 60
END
END AS cas,
cr.dr,
cr.minute_slot,
cr.hour_slot
FROM test t
JOIN calendar_range cr
ON TSTZRANGE(t.s_start, t.s_end, '[)') && cr.dr;
Note the use of the && OVERLAPS operator.
Result:
user_id session_id s_start s_end cas dr minute_slot hour_slot
1 1 2021-02-25 10:10:00 2021-02-25 10:20:00 10 ["2021-02-25 10:10:00+00","2021-02-25 10:20:00+00") 1 10
1 2 2021-02-25 10:50:00 2021-02-25 10:55:00 5 ["2021-02-25 10:50:00+00","2021-02-25 11:00:00+00") 5 10
1 3 2021-02-25 11:40:00 2021-02-25 12:30:00 10 ["2021-02-25 11:40:00+00","2021-02-25 11:50:00+00") 4 11
...
... snipped for brevity
...
1 4 2021-02-26 11:46:00 2021-02-26 11:57:00 4 ["2021-02-26 11:40:00+00","2021-02-26 11:50:00+00") 4 11
1 4 2021-02-26 11:46:00 2021-02-26 11:57:00 7 ["2021-02-26 11:50:00+00","2021-02-26 12:00:00+00") 5 11
The slots are of 10 minutes duration (as per the question - for 1 hour long slots, check out the fiddle here) . I've also included an hour_slot field if you're interested in finding out which hour of the day your event are occurring. You can, of course, vary this according to your requirements - the principle is the same - you can SUM() and GROUP BY your various slots as needed.
Just note that the slot from 11:46 to 11:57 has been accounted for correctly - 4 minutes in the slot from 11:40 - 11:50 and 7 minutes in the slot 11:50 - 12:00. Be sure and check edge cases as they can be easily missed.
Just be sure that you understand the Inclusive/Exclusive bounds notation (beginning and ending square ([]) and round (())) brackets - and how to use them - any confusion can be the source of subtle, tricky-to-find bugs!
Finally, you appear to be using TIMESTAMPs with TIME ZONE - i.e. the TIMESTAMPTZ and their corresponding range types - this is a good thing! You should always use UTC for anything to do with timestamps - and not storing, for example, offsets - since this can vary according to DST (Daylight Savings Time).
Following comments:
How could I get rows for all ranges - also where there is no corresponding tab entry (where duration is zero, where user_id is null)?
SELECT
COALESCE(t.user_id, 0) AS u_id, COALESCE(t.session_id, 0) AS s_id,
t.s_start::TIME, t.s_end::TIME,
COALESCE(
CASE
WHEN (LOWER(cr.dr) >= t.s_start) AND (UPPER(cr.dr) <= t.s_end) THEN 10
ELSE
CASE
WHEN (LOWER(cr.dr) < t.s_start) AND (UPPER(cr.dr) <= t.s_end) THEN
EXTRACT(EPOCH FROM (UPPER(cr.dr) - t.s_start)) / 60
ELSE EXTRACT(EPOCH FROM (t.s_end - LOWER(cr.dr))) / 60
END
END, 0) AS "No. mins",
cr.dr,
cr.minute_slot,
cr.hour_slot
FROM calendar_range cr
LEFT OUTER JOIN test t
ON TSTZRANGE(t.s_start, t.s_end, '[)') && cr.dr
WHERE LOWER(cr.dr) >= '2021-02-25 10:00:00'
AND LOWER(cr.dr) < '2021-02-25 13:30:00';
Result:
Note the 5 minutes counted for session 2. Also, the range was restricted using the WHERE clause - so as not to have too many empty records. I used an image here rather than posting text because with all of the NULLs, it's very difficult to align records.
Ah, I think my examples gave an impression that I'm looking for 10-minute slots, sorry for that! I was only looking for 1-hour slots
The principle for hourly slots is exactly the same - see the fiddle here.
