SLA calculation in Postgresql

Question

I want to calculate the time between the registration date so far (current_timestamp). Ex: If the record was 2019-03-01 09:11:17 and now it’s 2019-03-01 09:46:17 then it should return me 00:35:00 minutes, right? Only this time should stop counting after 6:00 pm and only return after 8:00 pm the next day. Nor should it count on Saturdays, Sundays and holidays.

Answer 1

I don’t know if I understand you very well, but you want to know the records that are on business time?

If yes the solution commented earlier solves. Below is an example according to the previous answer, and also the complement with the isodow function, which serves to know which day of the week a certain date is. Follow example of consultation from Monday to Friday from 8 to 18 hours:

create table teste (
    datahora timestamp
);

Insert into teste values (current_timestamp);
Insert into teste values (current_timestamp + interval '1' day);
Insert into teste values (current_timestamp + interval '2' day);
Insert into teste values (current_timestamp + interval '3' day);
Insert into teste values (current_timestamp + interval '4' day);

select* from teste where (extract(hour from datahora) between 8 and 18) and (extract(isodow from datahora) between 1 and 5);

Complementing its need, to add the SLA of the datahour record but only from 8 to 18 hours of the weekdays, I set up a query with the clause WITH and WITH RECURSIVE, but could also have used a TEMP TABLE. It is worth researching a little about her and about CTE. Follow example:

Insert into teste values (current_timestamp + interval '-10' day);
Insert into teste values (current_timestamp + interval '-12' day);
Insert into teste values (current_timestamp + interval '-15' day);

with recursive sla_dias_anteriores (datahora_original, datahora_inicial, datahora_final, diferenca_horas) as (
    select date_trunc('sec', datahora) as datahora_original, date_trunc('sec', datahora) as datahora_inicial, (date_trunc('day', datahora) +  interval '18' hour) as datahora_final, ((date_trunc('day', datahora) +  interval '18' hour) - date_trunc('sec', datahora)) as diferenca_horas from teste where (extract(hour from datahora) between 8 and 18) and (extract(isodow from datahora) between 1 and 5)
    union
    select datahora_original, 
    h.datahora_inicial as datahora_inicial,
    h.datahora_final as datahora_final, 
    ( h.datahora_final -  h.datahora_inicial) as diferenca_horas from sla_dias_anteriores as s
    , lateral (select (date_trunc('day', datahora_inicial) +  interval '1' day + interval '8' hour) as datahora_inicial, (date_trunc('day', datahora_final) +  interval '1' day + interval '18' hour) as datahora_final) as h
    where s.datahora_final <= current_timestamp
),
sla_dia_atual (datahora_original, datahora_inicial, datahora_final, diferenca_horas) as (
    select date_trunc('sec', datahora) as datahora_original, h.datahora_inicial as datahora_inicial, h.datahora_final as datahora_final, (h.datahora_final - h.datahora_inicial) as diferenca_horas
    from teste,
    lateral (select (date_trunc('day', current_timestamp::timestamp) +  interval '8' hour) as datahora_inicial, (date_trunc('sec', current_timestamp::timestamp)) as datahora_final) as h
    where (extract(hour from datahora) between 8 and 18) and (extract(isodow from datahora) between 1 and 5)
)
select q.datahora_original, sum (q.diferenca_horas) as soma_sla from (
    select * from sla_dias_anteriores where datahora_final <= current_timestamp 
    union 
    select * from sla_dia_atual) as q group by q.datahora_original;