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.
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
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
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