Calculate the duration in an interval and outside of events that can last over several days

I need to calculate the total duration of events in a given time interval and on a given day of the week with PostgreSQL 11

For an event on the same day no problem. But over several days I manage to approximate the result with "generate_series".u Is there another way faster and more precise?

Interval In:

``````Start---------------------------------------End
E.begin_date ------ E.end_date                         -> E.end_date -  E.begin_date

Start--------------------------------End
E.begin_date -------------------E.end_date         -> End - E.begin_date

Start--------------------------------End
E.begin_date -----------------------E.end_date                 -> E.end_date - Start

Start------------------End
E.begin_date-------------------------------------E.end_date    -> End - Start
``````

Interval In on several Days with generate_series

``````                            Day 1               Day 2 etc....
|                     |
Start-------End  |  Start---------End  |  Start---------End
E.begin_date--------------------------------------------------------------E.end_date
``````

Interval Out:

``````                Start--------End
E.begin_date ------------------- E.end_date    -> (Start - E.begin_date) + (E.end_date - End)

Start--------------------------------End
E.begin_date -------------------E.end_date         -> E.end_date  - End

Start--------------------------------End
E.begin_date -----------------------E.end_date                 -> Start - .begin_date

Start--------End
E.begin_date--------E.end_date    -> E.end_date - E.begin_date
``````

Interval Out on several Days with generate_series

``````                            Day 1               Day 2 etc....
|                     |
Start-------End  |  Start---------End  |  Start---------End
E.begin_date--------------------------------------------------------------E.end_date
``````

This is my sql request:

``````select * FROM (SELECT  sum(
case
when (begin_date AT TIME ZONE 'Europe/Paris')::date = (end_date AT TIME ZONE 'Europe/Paris')::date -- same days
then
case
-- ------time_begin------------------------------------------------------------------------time_end----
--                    begin_date------------------------------------------------end_date
when (begin_date AT TIME ZONE 'Europe/Paris')::time >= '08:00'::time
AND
(end_date AT TIME ZONE 'Europe/Paris')::time <= '18:00'::time
then (end_date AT TIME ZONE 'Europe/Paris')::time - (begin_date AT TIME ZONE 'Europe/Paris')::time

-- ------time_begin------------------------------------------------------------------------time_end----
--                                                 begin_date------------------------------------------------end_date
when (begin_date AT TIME ZONE 'Europe/Paris')::time >= '08:00'::time
AND
(begin_date AT TIME ZONE 'Europe/Paris')::time <= '18:00'::time
AND
(end_date AT TIME ZONE 'Europe/Paris')::time >= '18:00'::time
then '18:00'::time - (begin_date AT TIME ZONE 'Europe/Paris') ::time

-- -----------------time_begin------------------------------------------------------------------------time_end----
--    begin_date-----------------------------------end_date
when (end_date AT TIME ZONE 'Europe/Paris')::time >= '08:00'::time
AND
(end_date AT TIME ZONE 'Europe/Paris')::time <= '18:00'::time
AND
(begin_date AT TIME ZONE 'Europe/Paris')::time <= '08:00'::time
then (end_date AT TIME ZONE 'Europe/Paris')::time - '08:00'::time

-- -----------------time_begin-------------------------------------time_end----
--    begin_date----------------------------------------------------------------end_date
when (begin_date AT TIME ZONE 'Europe/Paris')::time <= '08:00'::time
AND
(end_date AT TIME ZONE 'Europe/Paris')::time >= '18:00'::time
then '18:00'::time - '08:00'::time
END
when (begin_date AT TIME ZONE 'Europe/Paris')::date < (end_date AT TIME ZONE 'Europe/Paris')::date -- on many days
then end_date - begin_date
(select COUNT(*) * interval '5 min'
FROM generate_series(begin_date AT TIME ZONE 'Europe/Paris', end_date AT TIME ZONE 'Europe/Paris', interval '5 min') m
where (m::time AT TIME ZONE 'Europe/Paris')  >= '08:00'::time
AND (m::time AT TIME ZONE 'Europe/Paris') <= '18:00'::time
AND EXTRACT(dow FROM m) + 1 in (2, 3)
)

END) as total_included
FROM "event"
WHERE ( (EXTRACT(dow FROM "event"."begin_date" AT TIME ZONE 'Europe/Paris') + 1 in (2, 3) OR
EXTRACT(dow FROM "event"."end_date" AT TIME ZONE 'Europe/Paris') + 1 in (2, 3) )
AND ("event"."begin_date" AT TIME ZONE 'Europe/Paris')::date >= '2019-12-01T00:00:00'::date
AND ("event"."begin_date" AT TIME ZONE 'Europe/Paris')::date <= '2020-01-01T00:00:00'::date
AND (
((event.begin_date AT TIME ZONE 'Europe/Paris')::time between '08:00'::time AND '18:00'::time)
OR
((event.end_date AT TIME ZONE 'Europe/Paris')::time between '08:00'::time AND '18:00'::time)
OR (
((event.begin_date AT TIME ZONE 'Europe/Paris')::time < '08:00'::time)
AND
((event.end_date AT TIME ZONE 'Europe/Paris')::time > '18:00'::time))
))
order by total_included desc) as included,

(SELECT sum(
case
when (begin_date AT TIME ZONE 'Europe/Paris')::date = (end_date AT TIME ZONE 'Europe/Paris')::date -- same day
then
case
-- -----------------------------------------------------------------time_begin---------------------------------time_end----
--          begin_date-------------------------------end_date
-- or
-- -------------------------------time_begin---------------------------------time_end-----------
--          begin_date----------------------------------------------------------------------end_date
when ((begin_date AT TIME ZONE 'Europe/Paris')::time NOT BETWEEN '08:00'::time AND '18:00'::time)
AND
((end_date AT TIME ZONE 'Europe/Paris')::time NOT BETWEEN '08:00'::time AND '18:00'::time)
then
case
when ((begin_date AT TIME ZONE 'Europe/Paris')::time < '08:00'::time) AND ((end_date AT TIME ZONE 'Europe/Paris')::time < '08:00'::time)
then end_date::time - begin_date::time
when (begin_date AT TIME ZONE 'Europe/Paris')::time > '18:00'::time AND (end_date AT TIME ZONE 'Europe/Paris')::time > '18:00'::time
then end_date::time - begin_date::time
when (begin_date AT TIME ZONE 'Europe/Paris')::time < '08:00'::time AND (end_date AT TIME ZONE 'Europe/Paris')::time > '18:00'::time
then ('08:00'::time - (begin_date AT TIME ZONE 'Europe/Paris')::time) + ((end_date AT TIME ZONE 'Europe/Paris')::time - '18:00'::time)
end
-- --------------------------------------time_begin---------------------------------time_end----
--          begin_date-----------------------------------end_date
when ((begin_date AT TIME ZONE 'Europe/Paris')::time NOT BETWEEN '08:00'::time AND '18:00'::time)
AND
((end_date AT TIME ZONE 'Europe/Paris')::time BETWEEN '08:00'::time AND '18:00'::time)
then '08:00'::time - (begin_date AT TIME ZONE 'Europe/Paris')::time

-- ----------time_begin---------------------------------time_end----
--                                 begin_date-----------------------------------end_date
when ((begin_date AT TIME ZONE 'Europe/Paris')::time BETWEEN '08:00'::time AND '18:00'::time)
AND
((end_date AT TIME ZONE 'Europe/Paris')::time NOT BETWEEN '08:00'::time AND '18:00'::time)
then (end_date AT TIME ZONE 'Europe/Paris')::time - '18:00'::time
end
when (begin_date AT TIME ZONE 'Europe/Paris')::date < (end_date AT TIME ZONE 'Europe/Paris')::date --many days
then
(select COUNT(*) * interval '5 min'
FROM generate_series(begin_date , end_date, interval '5 min') m
where (m AT TIME ZONE 'Europe/Paris')::time  >= '08:00'::time
AND (m AT TIME ZONE 'Europe/Paris')::time <= '18:00'::time
AND EXTRACT(dow FROM m AT TIME ZONE 'Europe/Paris') + 1 in (2, 3)
)

END) as total_excluded
FROM "event"
WHERE ((EXTRACT(dow FROM "event"."begin_date" AT TIME ZONE 'Europe/Paris') + 1 in (2, 3) OR
EXTRACT(dow FROM "event"."end_date" AT TIME ZONE 'Europe/Paris') + 1 in (2, 3) )
AND ("event"."date_creation" AT TIME ZONE 'Europe/Paris')::date >= '2019-12-01T00:00:00'::date
AND ("event"."date_creation" AT TIME ZONE 'Europe/Paris')::date <= '2020-01-01T00:00:00'::date
AND  (

((event.begin_date AT TIME ZONE 'Europe/Paris')::time not between '08:00'::time AND '18:00'::time)
OR
((event.end_date AT TIME ZONE 'Europe/Paris')::time not between '08:00'::time AND '18:00'::time)
OR  (
((event.begin_date AT TIME ZONE 'Europe/Paris')::time > '08:00'::time)
AND
((event.end_date AT TIME ZONE 'Europe/Paris')::time < '18:00'::time))
))
order by total_excluded desc) as excluded
``````

Data Sample for interval IN ('8am', '6pm') for Wednesday Thursday:

``````|---------|------------------|------------------|------------------|
|   id    |     begin_date   |     end_date     | Result(Duration) |
|---------|------------------|------------------|------------------|
|    1    | 2020-01-01 10:00 | 2020-01-01 12:00 |     02:00:00     |
|---------|------------------|------------------|------------------|
|---------|------------------|------------------|------------------|
|    2    | 2020-01-01 10:00 | 2020-01-01 20:00 |     08:00:00     |
|---------|------------------|------------------|------------------|
|---------|------------------|------------------|------------------|
|    3    | 2020-01-01 07:00 | 2020-01-01 14:00 |     06:00:00     |
|---------|------------------|------------------|------------------|
|---------|------------------|------------------|------------------|
|    4    | 2020-01-01 07:00 | 2020-01-01 19:00 |     10:00:00     |
|---------|------------------|------------------|------------------|
|---------|------------------|------------------|------------------|
|    5    | 2020-01-01 08:00 | 2020-01-03 18:00 |     20:00:00     |
|---------|------------------|------------------|------------------|
|---------|------------------|------------------|------------------|
|    6    | 2020-01-01 09:00 | 2020-01-05 17:00 |     16:00:00     |
|---------|------------------|------------------|------------------|
``````

Data Sample for interval OUT ('8am', '6pm') for Wednesday Thursday:

``````|---------|------------------|------------------|------------------|
|   id    |     begin_date   |     end_date     | Result(Duration) |
|---------|------------------|------------------|------------------|
|    1    | 2020-01-01 10:00 | 2020-01-01 12:00 |     00:00:00     |
|---------|------------------|------------------|------------------|
|---------|------------------|------------------|------------------|
|    2    | 2020-01-01 10:00 | 2020-01-01 20:00 |     02:00:00     |
|---------|------------------|------------------|------------------|
|---------|------------------|------------------|------------------|
|    3    | 2020-01-01 07:00 | 2020-01-01 14:00 |     01:00:00     |
|---------|------------------|------------------|------------------|
|---------|------------------|------------------|------------------|
|    4    | 2020-01-01 07:00 | 2020-01-01 19:00 |     02:00:00     |
|---------|------------------|------------------|------------------|
|---------|------------------|------------------|------------------|
|    5    | 2020-01-01 07:00 | 2020-01-03 18:00 |     02:00:00     |
|---------|------------------|------------------|------------------|
|---------|------------------|------------------|------------------|
|    6    | 2020-01-01 09:00 | 2020-01-05 17:00 |     02:00:00     |
|---------|------------------|------------------|------------------|
``````

The input parameters are:

• start date and end date
• start time and end time
• a list of the days of the week

To reach a result where we have the total duration of events within the interval, but also the total duration of events outside the interval of hours. All this for a list of days of the given week.

Days of the week means that if an event lasts several days say from Monday to Wednesday, but in the filter there are only Monday and Tuesday, the duration of Wednesday will not be counted.

The current query works but to handle events that take place over several days I use `generate_series`, which is not very efficient. The question is how to improve this query.

• Range types and the intersection operator are probably what you are looking for Apr 10, 2020 at 12:27
• Yes but when event are over several days I don't see how we can do that. Apr 10, 2020 at 13:08
• Can't run the query in the question, it has a syntax error in the middle. Anyway I've attempted an answer with a much simpler query, please check it out. Apr 15, 2020 at 11:43

Interpretation of the question

Given that an event starts at `begin_date` and is finished at `end_date`, the goal is to map this duration onto days/hours that seem like hours open to business, presumably to compute how much work time each event consumed.

Based on the query provided, the working hours are `08:00-18:00`. It's not clear how you determine the working days. Sometimes the condition in the query is `EXTRACT(dow FROM m) + 1 in (1)`, and at other places it's `EXTRACT(dow FROM "event"."begin_date" AT TIME ZONE 'Europe/Paris') + 1 in (2) OR EXTRACT(dow FROM "event"."end_date" AT TIME ZONE 'Europe/Paris') + 1 in (2) )`

The question says that there is a list of the days of the week in input but I don't see that in the query.

Solution with intersection of ranges

Below is a query that demonstrates how to use ranges to sum your "IN" intervals.

What it does:

• generate the `08:00-18:00` ranges for each day between the start and end of event
• filters the days to exclude based on the "day of week" number. If you had a calendar table instead, a join could be used.
• computes the intersection of these ranges with the ranges of the event
• sum the durations per event

Query

``````WITH working_days AS (
select
id,
tstzrange(
begin_date::date + '8 hours'::interval+ n*interval '1 day'
,begin_date::date + '18 hours'::interval + n*interval '1 day'
) AS ref
from
(select id, begin_date,
generate_series(0, extract(days from end_date-begin_date)::int, 1) as n
from event) AS day_numbers
where date_part('dow', begin_date::date + n*interval '1 day') between 1 and 5
),
ranges as (
select event.id,
ref * tstzrange(begin_date,end_date) AS t_inc
from working_days join event using(id)
)
select id,
sum(upper(t_inc)-lower(t_inc)) AS total_included
from ranges
group by id order by id
;
``````

Result

With the sample data the result is

`````` id | total_included
----+----------------
1 | 02:00:00
2 | 08:00:00
3 | 06:00:00
4 | 10:00:00
5 | 30:00:00
6 | 29:00:00
``````

This matches the question except for id=6, but that might be because the `dow` filter doesn't match yours.

As for the OUT intervals, if I understand correctly I think it's just a difference, that is `OUT = (end_date - begin_date) - IN` for each event.

• First of all thank you for your answer. For ID 6 in fact it's a mistake on my part on the dow. Every day in my initial request must be the same. The days of the week in the entry are in the clause : `AND (EXTRACT(dow FROM "billing_transaction". "begin_date" AT TIME ZONE 'Europe/Paris') + 1 in (1) OR EXTRACT(dow FROM "billing_transaction". "end_date" AT TIME ZONE 'Europe/Paris') + 1 in (1) )` I've edited my question and correct data. Your awser is good Apr 16, 2020 at 9:43
• The results of total_included look good, however, I'd like to add up all the events to get a total duration. I was thinking sum(sum..) but that's not possible Apr 16, 2020 at 9:44
• @Hadrien: you can remove the `group by id order by id` to have a grand total. Or you can move the whole query into a subquery and sum `total_included` at the top level. Apr 16, 2020 at 11:36
• @Hadrien: when substracting a range from a bigger range that entirely contains it, the result is two ranges, so that can't fit into a single tstzrange value. But if `excluded = (end_date-begin_date) - included`, why bother with a more complicated way to compute it? Apr 17, 2020 at 10:39
• Yes I understand, but for total_excluded for a event with begin_date `2019-12-01 18:00:00` and end_date `2019-12-02 08:00:00` with filter start_time `18:00`and end_time `23:00`and dow `Sunday` the result is 1 hour because all the time of the monday not matter because of dow filter Apr 17, 2020 at 12:57