I have trouble to calculate the number of hours in a range of time, let's get in context first; i have buses that make trips and each trip have a departure date and an arrival date of timestamp format.
To bill the driver's route, we must know how much time he drove during the day and night, the rule we have is the following (Day is between 06H00 and 21H:00) (Night is between 21H:00 and 06H00);
Example:
travel_id | departure | arrival
1 | 23/06/2019 23:30:00 | 24/06/2019 05:55:00
2 | 24/06/2019 17:15:00 | 25/06/2019 22:30:00
3 | 22/06/2019 07:00:00 | 22/06/2019 15:50:00
4 | 22/06/2019 15:25:00 | 22/06/2019 22:45:00
Duration:
for the trip 1: (6h and 25min) in the night - 0h in the day
for the trip 2: (10h and 30min) in the night - (18h and 45min) in the day
for the trip 3: 0h in the night - (8h and 50min) in the day
for the trip 4: (2h and 45min) in the night - (5h and 35min) in the day
I have a piece of script with IntervalMatch but that did not give the result I wanted. I can attach it if it's asked.
Any idea or solution proposal to calculate the number of hours day / night spent on a trip would be great. I can attach it after if you want.
I attach a qvd with the dataset; to make it simple:
Num : it's the id of the trip
service_origin_time : this is the departure date in timestamp
service_destination_time: this is the date of arrival in timestamp
