hockeyman
October 14, 2024
180 views
4 votes
5 Answers

I have a table that stores data about events with id, start_timestamp (timestamp with time zone), end_timestamp (timestamp with time zone) and duration (in seconds):

create table my_table(
  id bigint generated by default as identity primary key
, start_timestamp timestamptz
, end_timestamp timestamptz
, duration bigint);
insert into my_table values
 (0,    '2024-10-01 03:00:00+00',   '2024-10-01 15:00:00+00',   43200)
,(1,    '2024-10-02 05:00:00+00',   '2024-10-03 17:00:00+00',   129600)
,(2,    '2024-10-04 12:00:00+00',   '2024-10-07 09:45:00+00',   251100);

Now the data I need to get from it is The duration of each event during working hours, which means 9:00 – 18:00, Monday to Friday.

Below are the expected results from each row with an explanation:

Event ID #0

id	start_timestamp	end_timestamp	duration	working_hours_duration
0	2024-10-01 03:00:00+00	2024-10-01 15:00:00+00	43200	21600

The event started before working hours, the time from 2024-10-01 03:00:00+00 should be ignored, and the difference between 2024-10-01 09:00:00+00 – 2024-10-01 15:00:00+00 should be calculated. The expected result would be 21600 (seconds).

Event ID #1

id	start_timestamp	end_timestamp	duration	working_hours_duration
1	2024-10-02 05:00:00+00	2024-10-03 17:00:00+00	129600	61200

Now the event not only started before working hours, but also ended the next day, meaning that a gap between two working days should also be excluded. So, the expected result would be all working hours range for 2024-10-02, which would be 09:00 to 18:00, and 09:00 till 17:00 for the 2024-10-03. It totals up to 17h, so the expected result would be 61200 (seconds).

Event ID #2

id	start_timestamp	end_timestamp	duration	working_hours_duration
2	2024-10-04 12:00:00+00	2024-10-07 09:45:00+00	35820	24300

This one is the most complicated, as it also includes weekend, which should be subtracted from the duration.

So, the event starts at Friday (2024-10-04) 12:00. So all the time till the end of that working day should be included (18:00 - 12:00 = 6 hours). Then the weekend starts, so everything till start of the work hours on Monday (2024-10-07) 09:00 should be ignored. And on Monday the duration would be 09:45:00 - 09:00:00 = 00:45. All of it should be summed up, so the result is 6h45' = 24300 (seconds).

Question:

Is it something that’s possible to achieve solely on PostgreSQL side, by a query, or should I simply fetch the raw data and process it in the backend instead?

Answers

date_trunc('week',start_timestamp) gets you a Monday modnight of the week your range starts.
You can define a workday tstzrange(), setting the bounds by adding the adequate +'9:00'::time to that Monday midnight.
Cross join lateral generate_series(0,4) as day_steps spawns 5 rows for each of your input rows. Adding that to the reference Monday midnight gives the working hours on each workday.
To be able to span multiple weeks, do the same for weeks.
Use range_agg() to reduce all of these tstzranges to a single tstzmultirange that represents all working hour ranges on all workdays of each week your input rows stretch over.

_{demo at db<>fiddle}

create table your_table2 as
select your_table.*
      ,range_agg(tstzrange( date_trunc('week',start_timestamp)--monday
                            +'09:00'::time
                            +make_interval(  days  => day_steps
                                           , weeks => week_steps)
                           ,date_trunc('week',start_timestamp)
                            +'18:00'::time
                            +make_interval(  days  => day_steps
                                           , weeks => week_steps))) as workdays
from your_table
cross join lateral generate_series(0,4) as day_steps
cross join lateral generate_series(0,( date_trunc('week',end_timestamp)::date
                                      -date_trunc('week',start_timestamp)::date
                                     )/7) as week_steps
group by id;

You can intersect your inputs with their corresponding workday ranges with a * operator:

anymultirange * anymultirange → anymultirange
Computes the intersection of the multiranges.
'{[5,15)}'::int8multirange * '{[10,20)}'::int8multirange → {[10,15)}
Unnest() the resulting intersection of ranges and sum() their upper()-lower() widths.

select id
      ,start_timestamp
      ,end_timestamp
      ,duration_over_workdays
      ,extract(epoch from duration_over_workdays) as in_seconds
from your_table2
cross join lateral (
  select sum(upper(r)-lower(r)) as duration_over_workdays
  from unnest(tstzmultirange(tstzrange(start_timestamp,end_timestamp,'[]'))
              *workdays) as r)_;

id	start_timestamp	end_timestamp	duration_over_workdays	in_seconds
0	2024-10-01 03:00:00+00	2024-10-01 15:00:00+00	06:00:00	21600.000000
1	2024-10-02 05:00:00+00	2024-10-03 17:00:00+00	17:00:00	61200.000000
2	2024-10-04 12:00:00+00	2024-10-07 09:45:00+00	06:45:00	24300.000000
3	2024-10-09 12:00:00+00	2024-10-21 09:45:00+00	69:45:00	251100.000000

You can convert the resulting interval to number of seconds represented by integer, but I’d just keep the more flexible and adequate interval.

Ranges and multiranges in combination with a calender can solve this issue in plain SQL:

WITH r AS (
    SELECT id, range_agg(TSRANGE(g + '09:00'::TIME, g + '18:00'::TIME)) * TSMULTIRANGE(TSRANGE(start_time, end_time)) AS working_hours
    FROM GENERATE_SERIES('2024-10-01'::TIMESTAMP, '2024-11-01'::TIMESTAMP, INTERVAL '1 day') g(s)
        , (VALUES
               (0, '2024-10-01 03:00:00'::TIMESTAMP, '2024-10-01 15:00:00'::TIMESTAMP)
               ,(1, '2024-10-02 05:00:00'::TIMESTAMP, '2024-10-03 17:00:00'::TIMESTAMP)
               ,(2, '2024-10-04 12:00:00'::TIMESTAMP, '2024-10-07 09:45:00'::TIMESTAMP)
           ) AS sub(id, start_time, end_time)
    WHERE EXTRACT(DOW FROM s) BETWEEN 1 AND 5 -- weekdays
    GROUP BY id, start_time, end_time
), seconds_per_day AS (
    SELECT  id, working_hours
        ,   EXTRACT(EPOCH FROM UPPER(UNNEST(working_hours)) - LOWER(UNNEST(working_hours))) seconds
    FROM r)
SELECT id, SUM(seconds)
FROM seconds_per_day
GROUP BY id
ORDER BY id;

In this example I used generate_series(), better to replace this by a table that holds a complete calendar including holidays etc.

It’s the multirange operator * that does all the work to compute the intersection.

First, divide the multi-day intervals into rows of 1 day in each row, using generate_series(…).
Then we will join it to the work schedule table (timetable) on week day.

See example

with timetable as(
  select cast(dname as varchar(3)) dname,work_start::time,work_end::time
  from (values ('mon','9:00','18:00'),('tue','9:00','18:00')
              ,('wed','9:00','18:00'),('thu','9:00','18:00')
              ,('fri','9:00','18:00'),('sat','0:00','00:00')
              ,('sun','0:00','00:00')
       )t(dname,work_start,work_end)
)
select id,start_timestamp,end_timestamp,duration
  ,sum(extract('epoch' from (wday_end_dttm-wday_start_dttm))) new_duration
from(
  select *
    ,case when dd=0 then 
       dt+least(greatest(start_tm,work_start),work_end) 
     else dt+tt.work_start
     end wday_start_dttm
    ,case when dd=ddif then dt+greatest(least(end_tm,work_end),work_start) 
     else dt+tt.work_end
     end wday_end_dttm
  from(
     select *
       ,date_trunc('day',start_timestamp+(cast(dd as varchar)||' day')::interval ) dt
     from( 
       select id,start_timestamp,end_timestamp,duration
         ,start_timestamp::time start_tm,end_timestamp::time end_tm
         ,extract('day' from(date_trunc('day',end_timestamp)-date_trunc('day',start_timestamp))) ddif
       from test
     )a
     cross join generate_series(0,ddif,1)dd
  )b
  left join timetable tt on tt.dname=to_char(dt,'dy')
)c
group by id,start_timestamp,end_timestamp,duration
order by id

id	start_timestamp	end_timestamp	duration	new_duration
0	2024-10-01 03:00:00+00	2024-10-01 15:00:00+00	43200	21600.000000
1	2024-10-02 05:00:00+00	2024-10-03 17:00:00+00	129600	61200.000000
2	2024-10-04 12:00:00+00	2024-10-07 09:45:00+00	251100	24300.000000

fiddle

Another spin of the same idea, trying to avoid a whole calendar in the tstzmultirange: multiply whole days in the range that don’t fall on weekends by '9 hours', then only check the intersection of the start and end timestamps with workdays, if they happen on them:
_{demo at db<>fiddle}

with full_days_and_remainders as(
select*,(select '9 hours'::interval
               *count(*)filter(where date_part('dow',d)not in(6,0)) 
         from generate_series( start_timestamp::date+1
                              ,-1+end_timestamp::date,'1d')d)as full_workdays
       ,tstzmultirange(case when date_part('dow',start_timestamp) in (1,2,3,4,5) then
                       tstzrange( date_trunc('day',start_timestamp)+'09:00'::interval
                                 ,date_trunc('day',start_timestamp)+'18:00'::interval)
                       else tstzrange('empty') end
                      ,case when date_part('dow',end_timestamp) in (1,2,3,4,5) then
                       tstzrange( date_trunc('day',end_timestamp)+'09:00'::interval
                                 ,date_trunc('day',end_timestamp)+'18:00'::interval)
                       else tstzrange('empty') end)
        *tstzmultirange(tstzrange( start_timestamp
                                  ,end_timestamp)) as remainder_overlap
from your_table)
select(select sum(upper(r)-lower(r))+full_workdays
       from unnest(remainder_overlap)as r) as total_duration_over_workdays,*
from full_days_and_remainders;

total_duration_over_workdays	id	start_timestamp	end_timestamp	full_workdays	remainder_overlap
07:00:00	0	2024-10-01 04:00:00+01	2024-10-01 16:00:00+01	00:00:00	{["2024-10-01 09:00:00+01","2024-10-01 16:00:00+01")}
18:00:00	1	2024-10-02 06:00:00+01	2024-10-03 18:00:00+01	00:00:00	{["2024-10-02 09:00:00+01","2024-10-02 18:00:00+01"),["2024-10-03 09:00:00+01","2024-10-03 18:00:00+01")}
06:45:00	2	2024-10-04 13:00:00+01	2024-10-07 10:45:00+01	00:00:00	{["2024-10-04 13:00:00+01","2024-10-04 18:00:00+01"),["2024-10-07 09:00:00+01","2024-10-07 10:45:00+01")}
69:45:00	3	2024-10-09 13:00:00+01	2024-10-21 10:45:00+01	63:00:00	{["2024-10-09 13:00:00+01","2024-10-09 18:00:00+01"),["2024-10-21 09:00:00+01","2024-10-21 10:45:00+01")}

I would first create a helper function that splits the interval end_timestamp - start_timestamp into daily intervals. It might be useful for other cases too.

create or replace function daily_list(from_ts timestamptz, to_ts timestamptz)
returns setof timestamptz[] immutable parallel safe language sql as 
$$
  select array [greatest(s, from_ts), least(s + interval '1 day - 1 us', to_ts)] 
  from generate_series(date_trunc('day',from_ts),date_trunc('day',to_ts),interval 'P1D') s;
$$;

Then use a scalar subquery for working_hours_duration.

select my_table.*, extract(epoch from 
 (
   select sum(case when extract(isodow from dl[1]) not in (6, 7) -- skip holidays
           then least(dl[2]::time,'18:00') - greatest(dl[1]::time,'08:00') end)
   from daily_list(start_timestamp, end_timestamp) as dl
 ))::integer as working_hours_duration
from my_table;

id	start_timestamp	end_timestamp	duration	working_hours_duration
0	2024-10-01 03:00:00 +0300	2024-10-01 15:00:00 +0300	43200	25200
1	2024-10-02 05:00:00 +0300	2024-10-03 17:00:00 +0300	129600	68400
2	2024-10-04 12:00:00 +0300	2024-10-07 09:45:00 +0300	251100	27900

DB Fiddle demo

Please signup or login to give your own answer.

Click here to cancel reply.

Extract duration from interval during working hours in PostgreSQL

Question:

Answers