Presto: разница во времени в минутах между двумя временными метками только для рабочих часов.

Я пытаюсь найти разницу во времени в минутах между двумя датами, но я хочу, чтобы разница учитывала время, когда магазин был открыт. Магазин открыт только 9 часов в день и только в будние дни (выходные тоже закрыты), поэтому разница во времени должна учитывать время согласно часам работы.

Пример: 18.07.2019 9:50 - 22.07.2019 10:02

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'))

Это дает ответ как 5772. Но я хочу, чтобы ответ был 1092.

Эксель формула такая

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)

Эта версия у меня работает, но есть ли способ лучше? Кроме того, если бы мы могли написать функцию в presto, я не знаю, как написать вызываемую функцию в 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

postgresql presto datediff date-difference
person Bruce Wayne    schedule 14.10.2019    source источник
comment
Это может быть вашим решением: stackoverflow.com/questions/1839319/   -  person Erwin Brandstetter    schedule 15.10.2019
comment
не в состоянии заставить его работать. выберите x из unnest(sequence(cast(date_parse('22.07.2019 10:02:00','%m/%d/%Y %h:%i:%s %p') как дата), cast(date_parse('22.07.2019 10:02:00','%m/%d/%Y %h:%i:%s %p') как дата))) t(x) это не у меня тоже не работает! Попытка увидеть, что может работать   -  person Bruce Wayne    schedule 15.10.2019

Ответы (1)


arrow_upward
1
arrow_downward

Ну, я не знаю, какой запрос вы пытались выполнить, но я делаю то, чего нет, и это Postgres. В Postgres нет ни функций date_diff, ни date_parse, а также он не поддерживает спецификаторы формата %m/%d/... (SQL Server?)
Глядя на инструкцию Excel, она содержит макрос, который ваша инструкция не пытается выполнить для размещения: ЧИСТРАБДНИ. Этот макрос возвращает количество суббот, воскресений и праздников в указанный период данных. В Postgres все это можно преобразовать в один оператор SQL (ну, почти). Далее я обернул этот оператор в функцию SQL, которая возвращает желаемое значение, однако контрольный пример 1 никогда не бывает достаточным. Также я включил функцию SQL для возврата логического значения, если день является выходным (true, если оно ложно в противном случае). Но не зная ваших праздников в настоящее время, он всегда возвращает ложь.

-- 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);
person Belayer    schedule 16.10.2019
comment
Я делаю это на Presto - person Bruce Wayne; 17.10.2019