Postgres function to determine number of weekends in an interval

Question

I'm trying to update a stored procedure that determines a response time from when a ticket is received. In the table I have the timestamp when the ticket was received (ref_dttm TIMESTAMP WITHOUT TIME ZONE) and the timestamp when the ticket was first responded to (first_action_dttm TIMESTAMP WITHOUT TIME ZONE). When calculating the response time, I need to account for operating hours, weekends, and their holiday closures.

Currently the function calculates the interval and can subtract the hours they business is closed but I can't seem to figure out a way to exclude the weekends and holidays. Basically I'll need to subtract 15 hrs per week day (open 0900-1800) and 24 hrs for each weekend day and holiday.

Given the day of the week that the ticket is received and the time span:

Select 
    extract(dow from ref_dttm) as dow, 
    extract(days from (ref_dttm - first_action_dttm) as days

Is there a simple way to determine how many weekends have passed?

This is what I have so far - it subtracts 15 hrs per day and doesn't account for weekends:

CREATE TEMP TABLE tmp_ticket_delta ON COMMIT DROP AS
  SELECT id,ticket_id,ticket_num
    ,(ticket_dttm - first_action_dttm) as delta
    ,extract(days from (ticket_dttm - first_action_dttm)) as days
    ,ticket_descr
  FROM t_tickets
  WHERE ticket_action_by > 0

SELECT id,ticket_id,ticket_num,delta,days,ticket_descr,
  CASE WHEN days = 0 THEN
    CASE WHEN extract(hour from delta) > 15 THEN
      --less than one day but outside of business hours so subtract 15 hrs
      delta - INTERVAL '15:00:00.000'
    ELSE
      delta
    END
  ELSE
    CASE WHEN extract(hour from delta) > 15 THEN
      --take the total number of hours - closing hours + delta - closed hours
      (((days * 24) - (days * 15)) * '1 hour'::INTERVAL) + delta - INTERVAL '15:00:00.000' - (days * '1 day'::INTERVAL)
    ELSE
      (((days * 24) - (days * 15)) * '1 hour'::INTERVAL) + delta - (days * '1 day'::INTERVAL)
    END 
  END AS adj_diff
FROM tmp_ticket_delta

Answer 1

I like to store important business data in tables. Queries like this

select min(cal_date), 
       max(cal_date), 
       sum(hours_open) total_time_open, 
       sum(hours_closed) total_time_closed
from daily_hours_open_and_closed
where cal_date between '2013-08-28' and '2013-09-03';

are easy to understand, maintain, and debug when they're based on data stored in simple tables.

I'd start with a calendar table, and add a table for the time your place is open. This table, "open_times", is the simplest way to start, but it might be too simple for your business. For example, you might want tighter CHECK constraints. Also, I've made no attempt to make this efficient, although the final query runs in only 12 ms on my development box.

create table open_times (
  bus_open timestamp primary key,
  bus_closed timestamp not null
    check (bus_closed > bus_open)
);

Quick and dirty way to populate that table with weekday hours for 2013.

with openings as (
  select generate_series(timestamp '2013-01-01 09:00', 
                         timestamp '2013-12-31 18:00', '1 day') bus_open
)
insert into open_times
select bus_open, bus_open + interval '9 hours' bus_closed
from openings
where extract(dow from bus_open) between 1 and 5
order by bus_open;

Labor day is a holiday here, so Monday, Sep 2, is a holiday. Delete 2013-09-02.

delete from open_times
where bus_open = '2013-09-02 09:00';

That's the only holiday I'm interested in for the purpose of showing how this works. You'll have to do better than I did, of course.

To make things simpler still, create a view that shows the daily hours of operation as intervals.

create view daily_hours_open_and_closed as
select c.cal_date, 
       ot.bus_open,
       ot.bus_closed,
       coalesce(bus_closed - bus_open, interval '0 hours') as hours_open,
       interval '24 hours' - (coalesce(bus_closed - bus_open, interval '0 hours')) as hours_closed
from calendar c
left join open_times as ot 
       on c.cal_date = cast(ot.bus_open as date);

Now, how many hours are we open and how many hours are we closed for the 7 days between 2013-08-28 and 2013-09-03? The query for raw data is dead simple now.

select *
from daily_hours_open_and_closed
where cal_date between '2013-08-28' and '2013-09-03'
order by cal_date;

cal_date     bus_open              bus_closed            hours_open  hours_closed
--
2013-08-28   2013-08-28 09:00:00   2013-08-28 18:00:00   09:00:00    15:00:00
2013-08-29   2013-08-29 09:00:00   2013-08-29 18:00:00   09:00:00    15:00:00
2013-08-30   2013-08-30 09:00:00   2013-08-30 18:00:00   09:00:00    15:00:00
2013-08-31                                               00:00:00    24:00:00
2013-09-01                                               00:00:00    24:00:00
2013-09-02                                               00:00:00    24:00:00
2013-09-03   2013-09-03 09:00:00   2013-09-03 18:00:00   09:00:00    15:00:00

Use aggregate functions to do the arithmetic.

select min(cal_date), 
       max(cal_date), 
       sum(hours_open) total_time_open, 
       sum(hours_closed) total_time_closed
from daily_hours_open_and_closed
where cal_date between '2013-08-28' and '2013-09-03'

min          max          total_time_open   total_time_closed
--
2013-08-28   2013-09-03   36:00:00           132:00:00

Answer 2

You can count weekends by something like this query:

select
    *,
    (extract(week from ref_dttm) - extract(week from first_action_dttm)) * 2 -
    case extract(dow from first_action_dttm) when 0 then 1 else 0 end +
    case extract(dow from ref_dttm) when 0 then 2 when 6 then 1 else 0 end
from t_tickets

try it on sql fiddle demo

or if you dates could have different years:

select
    *,
    trunc(date_part('day', ref_dttm - first_action_dttm) / 7) * 2 + 
    case extract(dow from first_action_dttm) when 0 then 1 when 6 then 2 else 0 end + 
    case extract(dow from ref_dttm) when 6 then 1 when 0 then 2 else 0 end -
    case when extract(dow from ref_dttm) = extract(dow from first_action_dttm) then 2 else 0 end as weekends
from t_tickets

try it on sql fiddle demo

Answer 3

You can use generate_series to calculate number of Saturdays and Sundays in interval:

-- sample data
with t as (
   (select 1 as id, '2012-01-01'::timestamp as tstart, '2012-02-01'::timestamp as tend) union all -- 9
   (select 2 as id, '2011-12-31'::timestamp as tstart, '2012-02-04'::timestamp as tend) union all -- 11
   (select 3 as id, '2011-12-30'::timestamp as tstart, '2012-02-05'::timestamp as tend) union all -- 12
   (select 4 as id, '2011-12-30'::timestamp as tstart, '2012-02-07'::timestamp as tend)           -- 12
)

-- Calculate number of weekend days

select
   id, 
   sum((dow not between 1 and 5)::int) number_of_weekend_days
from
   (select id, extract(dow from generate_series(tstart,tend,'1 day')) as dow from t) x
group by
   id

I think this will be really slow if you have a lot of data.