How to add interval hours to a timestamp in postgres, excluding weekend hours

user_8750024 0
Viewed 376

I have a table in postgres with 2 columns; ordered_timestamp, weekday_hours.

I want to create a third column 'due_timestamp' which is calculated using 'ordered_timestamp' plus 'weekday_hours' ...BUT excluding the hours that fall across the weekend (12:00am Sat to 12:00am Mon)

ordered_timestamp    | weekday_hours  | due_timestamp
2020-06-04 16:00:00  |             12 | 2020-06-05 04:00:00
2020-06-05 16:00:00  |             48 | 2020-06-09 16:00:00

Without the caveat of excluding weekend hours, I could use ordered_timestamp + interval '1 hour' * weekday_hours

weekday_hours can vary from 1hr to hundreds of hours in some cases.

It seems this situation needs something additional to filter out the weekend hours.

recursive-query select date postgresql sql
2 Answers

If your date intervals may span over variying periods, I would use a quite brute force approach by enumarating all 1 hour intervals that fall between the timestamps with generate_series(), and then counting only those that belong to working days:

select t.ordered_timestamp, t.due_timestamp, x.weekday_hours
from mytable t
cross join lateral (
    select count(*) filter(where extract(dow from ts) between 1 and 5) - 1 weekday_hours 
    from generate_series(ordered_timestamp, due_timestamp, '1 hour'::interval) s(ts)
) x

Demo on DB Fiddle:

ordered_timestamp   | due_timestamp       | weekday_hours
:------------------ | :------------------ | ------------:
2020-06-04 16:00:00 | 2020-06-05 04:00:00 |            12
2020-06-05 16:00:00 | 2020-06-09 16:00:00 |            48
user_106767160

something like this?

case when  (EXTRACT(DOW FROM  ordered_timestamp ) = 6  )
     then  ordered_timestamp + interval '1 hour' * (weekday_hours+48)
     when  (EXTRACT(DOW FROM  ordered_timestamp ) = 0  )
     then  ordered_timestamp + interval '1 hour' * (weekday_hours+24)
 else
     ordered_timestamp + interval '1 hour' * weekday_hours
 end
user_50936220
Related