Generate date series with hours of 'OFF' type for each day

Question

I am writing a #PostgreSQL query for my company to calculate how many hours a person is OFF working, with a table containing information about requests, with these columns: Request ID, Sender username, Type, Start time, End time.

The sample data for this is:

Request ID	Sender username	Type	Start time	End time
1	Smith	OFF	2023-04-01 8:00:00	2023-04-03 13:00:00

My company working hours are from 8:30 to 18:00, and lunch break is from 12:00 to 13:30. I want to write a query that counts how many hours for OFF each day (lunch time not included), and returns this desired result:

Sender username	Date	Off(hours)
Smith	2023-04-01	8
Smith	2023-04-02	8
Smith	2023-04-03	3.5

Desired result table
For the date 2023-04-01, 8 hours because we only count a number of OFF hours that is in working time from 8:30 to 18:00.
For the date 2023-04-02, 8 hours because All the days between the start time date and end time date is 8 hours.
For the date 2023-04-03, 3.5 hours because since end time (13:00) is in lunch break time, we only count from 8:30 to 12:00

I finish the query for calculating the number of hours for all requests with start time and end time on the same day. For other requests the start time and end time are on different days, so I'm still not able to do them. Please help me.

Answer 1

Something like the following should do what you want:

with cte_ontimes as
(SELECT am_start as start_time, 
        am_start + interval '210 minute' as end_time 
FROM generate_series
        ( '2023-03-28 08:30:00'::timestamp 
        , '2023-04-10 08:30:00'::timestamp
        , '1 day'::interval) am_start
UNION
SELECT pm_start as start_time, 
        pm_start + interval '270 minute' as end_time 
FROM generate_series
        ( '2023-03-28 13:30:00'::timestamp 
        , '2023-04-10 13:30:00'::timestamp
        , '1 day'::interval) pm_start
ORDER BY 1),
cte as
(SELECT extract(hours 
                from least(o.end_time, f.end_time) 
                - greatest(o.start_time, f.start_time)) 
        + extract(minutes 
                  from least(o.end_time, f.end_time) 
                  - greatest(o.start_time, f.start_time)) / 60.0
    as hoursoff, 
    o.end_time::date as workday, f.sender_username 
FROM cte_ontimes o
INNER JOIN offtimes f ON f.start_time < o.end_time
and f.end_time > o.start_time AND f.type = 'OFF')
select sum(hoursoff) as daily_hours_off, workday, sender_username
FROM cte
GROUP BY workday, sender_username
ORDER BY workday, sender_username;

However, I strongly advise you to have a table listing the company worktimes, instead of a generate_series as I have used in the example. Why? Weekends can be got around, but public holidays would be very difficult to include, not to mention special half-days etc.

By way of explanation, I first select those dates and times where the "off" times coincide with the company worktimes. I then use the greatest and least functions to deal with any overlap - for example if your sample off time had started at 09:00 instead of 08:00, it would have been within the work times, and so we would want to count from 09.00 not 08:30. Having then got the times, it is relatively trivial to extract the hours and partial hours (minutes / 60). All that then remains, is to sum by date and user.