Split row into multiple rows by date

[Lena_Sofie]

Hello,

my goal is to split machine events at shift and day changes.

I have a list of machine events that are defined by an eventname and a timestamp fort he beginning of the event.

rowID	timestamp	eventname
1	03/04/2020 15:46:13	machine working
2	03/04/2020 16:15:35	error
3	03/04/2020 16:30:22	machine working
4	03/04/2020 23:55:18	setup
5	04/04/2020 03:17:51	machine working
6	04/04/2020 05:02:42	setup
7	04/04/2020 05:31:12	machine working
8	04/04/2020 05:59:59	stop for weekend
9	06/04/2020 06:00:00	machine working

For analyzing the duration of the events I calculated the time to the next change of events. These are allocated to the event start.

Start:
LOAD
rowid,
"timestamp",
"timestamp"as event_start
FROM [lib://AttachedFiles/Laufzeit.xml]
(XmlSimple, table is [data/slavestate]);

End:
Load
rowid,
event_start,
Peek(event_start, rowid, 'Start') as event_end
resident Start
order By rowid,event_start;

Duration:
Load
rowid,
event_start,
event_end,
(event_end-event_start)*24*60 as event_duration_min
resident End
order By rowid, event_start;

rowID	timestamp	eventname	event_start	event_end	event_duration_min
1	03/04/2020 15:46:13	machine working	03/04/2020 15:46:13	03/04/2020 16:15:35	29
2	03/04/2020 16:15:35	error	03/04/2020 16:15:35	03/04/2020 16:30:22	15
3	03/04/2020 16:30:22	machine working	03/04/2020 16:30:22	03/04/2020 23:55:18	445
4	03/04/2020 23:55:18	setup	03/04/2020 23:55:18	04/04/2020 03:17:51	204
5	04/04/2020 03:17:51	machine working	04/04/2020 03:17:51	04/04/2020 05:02:42	105
6	04/04/2020 05:02:42	setup	04/04/2020 05:02:42	04/04/2020 05:31:12	30
7	04/04/2020 05:31:12	machine working	04/04/2020 05:31:12	04/04/2020 05:59:59	29
8	04/04/2020 05:59:59	stop for weekend	04/04/2020 05:59:59	06/04/2020 06:00:00	2880
9	06/04/2020 06:00:00	machine working	06/04/2020 06:00:00

shifts: morning 6-14, afternoon 14-22, night 22-6

Now I want to do some analysis about the eventduration during a shift/day. Here occurs the problem: Some events last during a shiftchange/midnight and are therefore allocated to the previous shift/day… and distort my analysis.

How can I split these events to all affected shifts/days?

Help is highly apprechiated!