Calculate Working Hours in interval

See this thread to inform yourself

https://www.experts-exchange.com/questions/21449885/Adding-Week-Days-to-a-Date.html

This is a complicated problem and one you need to define the requirements for weekends, religious festivals, bank holidays, shift patterns, lunch breaks, dentists appointments etc.

ps You can simply subtract timestamp from the other to get the interval...

a simple approach if Closure date and creation dates are on the same day...
select min(date_trunc('day',Closure_date_UTC)  + Work_Period_end::time , Closure_date_UTC)  -
max(date_trunc('day',Creation_Date_UTC) + Work_Period_start::time, Creation_Date_UTC)
as working_hours from your_query;

Hey Earthman,
Thanks for the pointers!
I had those in mind, but right now, I don't worry about weekends, religious festivals, shift patterns etc. Although those features would be nice, I know they are more difficult and need dedicated professional time.

In my case I will have an interval of Monday to Friday to calculate on.

Can your script be used like that?

Thanks again Earthman!

These are some of the results I am receiving after calling the function:

2010-11-07 17:09:00      2010-11-08 04:18:00      NULL
2010-11-05 08:23:00      2010-11-08 08:06:00      NULL
2010-11-05 08:26:00      2010-11-05 08:27:00      -00:33:00

2010-11-05 09:44:00      2010-11-08 08:19:00      NULL
2010-11-05 10:50:00      2010-11-05 13:13:00      02:23:00
2010-11-05 12:22:00      2010-11-05 20:56:00      05:38:00

2010-11-05 01:14:00      2010-11-05 01:19:00      -07:41:00
2010-11-05 02:49:00      2010-11-05 03:05:00      -05:55:00
2010-11-05 05:47:00      2010-11-05 07:58:00      -01:02:00

I think it's working good, but are the "minus" results supposed to be there?

test1=> create or replace function working_hours( start_ts timestamp, end_ts timestamp, wp_start time, wp_end time ) returns interval as $$
test1$> declare
test1$>   x interval := interval '0 seconds';
test1$>   d1 date := date_trunc('day', start_ts);
test1$>   d2 date := date_trunc('day', end_ts);
test1$>   x1 timestamp := greatest(d1 + wp_start, start_ts);
test1$>   x2 timestamp := least(d2 + wp_end, end_ts);
test1$>   wd interval;
test1$>   dow integer := date_part( 'dow', d2 );
test1$> begin
test1$>   if ( dow = 1 and end_ts < (d2 + wp_start) ) then
test1$>     d2 := d2 - 3;
test1$>     x2 := d2 + wp_end;
test1$>   elsif ( dow = 0 ) then
test1$>     d2 := d2 - 2;
test1$>     x2 := d2 + wp_end;
test1$>   elsif ( dow = 6 ) then
test1$>     d2 := d2 - 1;
test1$>     x2 := d2 + wp_end;
test1$>   end if;
test1$>   dow := date_part( 'dow', x1 );
test1$>   if ( dow = 5 and start_ts > (d1 + wp_end) ) then
test1$>     d1 := d1 + 3;
test1$>     x1 := d1 + wp_start;
test1$>   elsif ( dow = 0 ) then
test1$>     d1 := d1 + 1;
test1$>     x1 := d1 + wp_start;
test1$>   elsif ( dow = 6 ) then
test1$>     d1 := d1 + 2;
test1$>     x1 := d1 + wp_start;
test1$>   end if;
test1$>
test1$>   if d1 = d2 and dow > 0 and dow < 6 then
test1$>     if ( x2 > x1 ) then
test1$>       x := x2 - x1;
test1$>     end if;
test1$>   else
test1$>     wd := wp_end - wp_start;
test1$>     if ( x2 > x1 ) then
test1$>       if ( (d1+wp_end) > x1 ) then
test1$>         x := (d1+wp_end) - x1;
test1$>       end if;
test1$>       d1 := d1 + 1;
test1$>       while ( d1 < d2 ) loop
test1$>         dow := date_part( 'dow', d1 );
test1$>         if ( dow > 0 and dow < 6 ) then
test1$>           x := x + wd;
test1$>         end if;
test1$>         d1 := d1 + 1;
test1$>       end loop;
test1$>       if ( d1 <> d2 and (d2+wp_start) < x2 ) then
test1$>         x := x + ( x2 - (d2 + wp_start));
test1$>       end if;
test1$>     end if;
test1$>   end if;
test1$>   return x;
test1$> end;
test1$> $$ LANGUAGE plpgsql;
CREATE FUNCTION
test1=> create or replace function working_hours( start_ts timestamp, end_ts timestamp, wp_start time, wselect id,Creation_Date_UTC,Closure_Date_UTC, working_hours( Creation_Date_UTC,Closure_Daee_UTC,'09:00:00'::time,'17:00:00'::time) from xi;
 id |     creation_date_utc      |      closure_date_utc      | working_hours
----+----------------------------+----------------------------+---------------
  1 | 2010-11-26 07:49:23.570259 | 2010-11-26 17:01:23.570259 | 08:00:00
  2 | 2010-11-07 17:09:00        | 2010-11-08 04:18:00        | 00:00:00
  3 | 2010-11-05 08:23:00        | 2010-11-08 08:06:00        | 08:00:00
  4 | 2010-11-05 08:26:00        | 2010-11-05 08:27:00        | 00:00:00
  5 | 2010-11-05 09:44:00        | 2010-11-08 08:19:00        | 07:16:00
  6 | 2010-11-05 10:50:00        | 2010-11-05 13:13:00        | 02:23:00
  7 | 2010-11-05 12:22:00        | 2010-11-05 20:56:00        | 04:38:00
  8 | 2010-11-05 01:14:00        | 2010-11-05 01:19:00        | 00:00:00
  9 | 2010-11-05 02:49:00        | 2010-11-05 03:05:00        | 00:00:00
 10 | 2010-11-05 05:47:00        | 2010-11-05 07:58:00        | 00:00:00
(10 rows)

test1=>

This is great Earthman, thanks alot!
I've run some tests and found that sometimes it seems to return incorrect results.

working_hours("Start_date_UTC", "Restoration_date_UTC", '05:00:00'::time without time zone, '16:00:00'::time without time zone)

Restoration_date_UTC      Start_date_UTC      working_hours      actual_result      Differences
02.05.2009 10:55         01.04.2009 10:19      236:41:00           247,6833333         10,98333333
08.01.2010 06:59         03.04.2009 07:28      2197:32:00         2199,516667         2,016666667
30.04.2009 09:50         06.04.2009 11:12      191:48:00           196,6333333         4,833333333

The results from actual_result column are from a VBA function we are using to calculate working hours. Based on the Differences column we can see that the sql workin_hours function is skipping a few hours. Where's the error?

Again, thank you very much for your help Earthman!
I am grateful!