Spreadsheet Libreoffice calc - pivot table - How to group chuncks of time seperated by a minimum of 30 mins

Question

I have a report in libreoffice calc which contains 2 sheets. The first one is a data sheet and the second one is a "report" sheet (with pivot tables).

In the first data sheet, there are 3 important columns, the first column has dates, the second column has time stamps and the third column has a product code called "Type".

I'm trying to create a pivot table that would automatically show per date, per "chuncks of time" seperated by a minimum of 30 mins, the count of type per type.

The trouble comes from how to create the chunks of time. There needs to be at least a 30 mins between 2 time stamp to qualify as a chunck of time.

for example, Data sheet:

Date	Time	Type
17.01.22	9:13	12
17.01.22	9:14	12
17.01.22	9:15	17
17.01.22	9:20	17
17.01.22	9:22	17
17.01.22	12:28	17
17.01.22	12:42	17
17.01.22	15:16	17
17.01.22	15:42	17
17.01.22	15:55	13
17.01.22	15:58	13

The end result in a pivot table should be:

Date	Time-Frame	Type	Count-Type
17.01.22	9:13-9:22	12	2
17.01.22	9:13-9:22	17	3
17.01.22	12:28-12:42	17	2
17.01.22	15:16-15:58	17	2
17.01.22	15:16-15:58	13	2

or

Date	Time-Frame	Type	Count-Type
17.01.22	9:13-9:22	12	2
		17	3
17.01.22	12:28-12:42	17	2
17.01.22	15:16-15:58	17	2
		13	2

Let me know if you have any question. And thank you in advance.

Answer 1

At the end I went this route: https://ask.libreoffice.org/t/how-to-group-chuncks-of-time-seperated-by-a-minimum-of-30-mins-pivot-table/74936

Which creates a helper column with this forumla: in C2:=IF((N(A2)+N(B2)-N(A1)-N(B1))>(1/48);N(C1)+1;N(C1)

Then end result is:

Date	Time	Type
17.01.22	9:13	1
17.01.22	9:14	1
17.01.22	9:15	1
17.01.22	9:20	1
17.01.22	9:22	1
17.01.22	12:28	2
17.01.22	12:42	2
17.01.22	15:16	3
17.01.22	15:42	3
17.01.22	15:55	3
17.01.22	15:58	3