sql help - generate HH slots based on timeslices

From: Jeff Chirco <backseatdba_at_gmail.com>
Date: Mon, 11 Sep 2017 13:37:29 -0700
Message-ID: <CAKsxbLr4F=+GMXtvq3kxQS4c0GFwajOxAbrqCHtLf6iEo68=Xw_at_mail.gmail.com>

Hi all I am need of some SQL help. Maybe because it is a Monday or my brain is just slow today. I am having trouble figuring out how to do this. I have IN and OUT time slices for an employee and I need to break that into half hour slices and the number of minutes worked in that half, relating to the business date or labor date.
So for example say I had a table like below:

create table labor_data (
emp_id number,
labor_date date,
in_time date,
out_time date);

insert into labor_data (EMP_ID, LABOR_DATE, IN_TIME, OUT_TIME) values (10000, to_date('01-09-2017', 'dd-mm-yyyy'), to_date('01-09-2017 16:30:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('01-09-2017 21:28:00',
'dd-mm-yyyy hh24:mi:ss'));

insert into labor_data (EMP_ID, LABOR_DATE, IN_TIME, OUT_TIME) values (10000, to_date('01-09-2017', 'dd-mm-yyyy'), to_date('01-09-2017 22:04:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('02-09-2017 02:25:00',
'dd-mm-yyyy hh24:mi:ss'));

commit;

I would like to generate something like below. I am flexible in how the half hours slots look like.

emp

labor_date

hh_slot

hh_time

minutes

10000

9/1/2017

1630