Presto: Time difference in minutes between two timestamps only for working hours

Question

I am trying to find the time difference in minutes between two dates, but I want the difference to account for the time the shop was open. The shop is open only 9 hours a day and only on weekdays (weekends are closed too), thus the time difference should account for the time as per the operating hours.

Example: 7/18/2019 9:50 AM - 7/22/2019 10:02 AM

SELECT date_diff('minute', date_parse('7/18/2019 9:50:00 AM','%m/%d/%Y %h:%i:%s %p'), date_parse('7/22/2019 10:02:00 AM','%m/%d/%Y %h:%i:%s %p'))

This gives the answer as 5772. But I want the answer to be 1092.

The excel formula is

A1 = 7/18/2019 9:50 AM
B1 = 7/22/2019 10:02 AM
if(OR(A1="",A1="NULL",B1="",B1="NULL"),"",(A1-B1)*24*60 - (days(A1,B1)+1-NETWORKDAYS(A1,B1))*24*60 - (NETWORKDAYS(A1,B1)-1)*15*60)

This version is working for me, but is there a better way? Also, if we could write a function on presto, I am unaware of how to write a callable function on presto.

with working_days AS
(
select count(*) as wd from unnest(sequence (cast('2017-06-07' as date), cast('2017-06-13' as date))) t(x)
WHERE extract (day_of_week from x) < 6 
)
select date_diff('minute', cast('2017-06-07 11:19:11.287' as timestamp), cast('2017-06-13 09:53:14.750' as timestamp)) - 
       ((date_diff('day', cast('2017-06-07' as date), cast('2017-06-13' as date))+1 - wd)*24*60 + (wd - 1)*16*60)
FROM working_days

Answer 1

Well I don't know what the query you attempted is, but I do what it's not - and that's Postgres. Postgres does not have the functions date_diff nor date_parse, and neither does it support the format specifiers %m/%d/... (SQL Server?)
Looking at the Excel statement it contains a macro your statement does not attempt to accommodate: NETWORKDAYS. This macro returns the number of Sat, Sun and Holidays within the data period specified. In Postgres the entire thing can be converted into a single SQL statement (well almost). In the following I've wrapped that statement in a SQL function that returns your desired value, however a test case of 1 is never sufficient. Also I've included a SQL function to return a boolean if a day is a holiday (true is it is false otherwise). But not knowing your holidays at present it always returns false.

-- Holiday Routine
create or replace function Is_Holiday(date_in timestamp)
returns boolean
language sql  
as $$
    -- TODO: determine if parameter is a holiday. if so return true
    select false;
$$;

--- Main Routine
create or replace function Work_Minuets_in_Period(date1_in timestamp, date2_in timestamp) 
returns integer
language sql strict
as $$
     with proper_dates as
         ( select least (date1_in,date2_in) start_date, greatest(date1_in, date2_in) end_date)
       , date_interval as (select end_date - start_date intv from proper_dates)
       , non_work_days as
         (select count(*)::double precision non_work                  -- for operation with extract( ... ) function 
            from (select generate_series            
                         ( start_date
                         , end_date
                         , interval '1 day'
                         ) this_day
                   from proper_dates  ) ds
           where extract(isodow from this_day ) in (6,7)             -- sat, sun
              or is_holiday(this_day)                                -- holiday?
         )
     select (9*60*(extract(days from intv) - non_work)               -- full work days (9 hr each)
            +  60*extract(hours from intv)                           -- partial hours from interval
            +  extract(minutes from intv) )::integer                 -- minuets
       from date_interval, non_work_days; 
$$;             

--- test (calling function)
select * from Work_Minuets_in_Period('7/22/2019 10:02 AM'::timestamp ,'7/18/2019 9:50 AM'::timestamp);