DESAFIO - PostgreSQL - Cálculo de Horas Úteis
05/08/2011
0
Olá amigos!
Tenho um desafio... hoje tenho um cálculo em SQL no postgres, que calcula entre dois campos timestamp, retornando a média de minutos, porém preciso deste cálculo considerando horas útes, ou seja, horário comercial.
Abaixo a fórmula atual:
qryEstatistic.SQL.Add('select avg(extract(epoch from (dth_status_prog - dth_proc))/60) as tempo_med_prog,');
O campo dth_proc é a data e hora em que o processo é lançado no sistema. O campo dth_status_prog é a data e hora em que o processo recebeu uma marcação. Gostaria de calcular o "horário útil" entre estes dois, pois seria injusto calcular o tempo em que o funcionário leva pra fazer a marcação levando em consideração horas corridas.
Lançado o desafio,
Abraço a todos!
Tenho um desafio... hoje tenho um cálculo em SQL no postgres, que calcula entre dois campos timestamp, retornando a média de minutos, porém preciso deste cálculo considerando horas útes, ou seja, horário comercial.
Abaixo a fórmula atual:
qryEstatistic.SQL.Add('select avg(extract(epoch from (dth_status_prog - dth_proc))/60) as tempo_med_prog,');
O campo dth_proc é a data e hora em que o processo é lançado no sistema. O campo dth_status_prog é a data e hora em que o processo recebeu uma marcação. Gostaria de calcular o "horário útil" entre estes dois, pois seria injusto calcular o tempo em que o funcionário leva pra fazer a marcação levando em consideração horas corridas.
Lançado o desafio,
Abraço a todos!
Marcelo Nascimento
Curtir tópico
+ 0
Responder
Posts
29/01/2020
Caio Cruz
Olá Marcelo, pesquisei ontem sobre o mesmo assunto e não encontrei nada além desse post, então fiz uma function pra isso:
Pra saber quanto tempo levou pra você ter essa resposta:
SELECT * FROM fn_tempo_util('2011-08-05 08:00:00', '2020-01-29 11:50:00');
---
19919:50:00 (horas úteis)
CREATE OR REPLACE FUNCTION fn_dia_util ( TIMESTAMP ) RETURNS INTEGER AS $$
DECLARE flg_dia_util INTEGER;
BEGIN
IF EXTRACT(ISODOW FROM $1) IN (6,7) THEN
flg_dia_util := 0;
ELSE
flg_dia_util := 1;
END IF;
RETURN flg_dia_util;
END; $$ LANGUAGE 'plpgsql';
--SELECT * FROM fn_dia_util ( CURRENT_DATE );
CREATE OR REPLACE FUNCTION fn_tempo_util ( TIMESTAMP, TIMESTAMP ) RETURNS INTERVAL AS $$
DECLARE p_entrada TIME := '09:00:00';
p_saida TIME := '18:00:00';
p_dif INTERVAL;
p_data DATE := $1::DATE;
p_minutos INTERVAL := INTERVAL '0 seconds';
BEGIN
-- se as datas sao do mesmo dia
IF $1::DATE = $2::DATE THEN
-- a conta fica na data fim menos data inicio (respeitando horario comercial)
p_dif := ((CASE WHEN $2::TIME > p_saida THEN $2::DATE + p_saida ELSE $2 END) - (CASE WHEN $1::TIME < p_entrada THEN $1::DATE + p_entrada ELSE $1 END))::INTERVAL;
-- se a data inicial e menor que a final
ELSEIF $1 < $2 THEN
-- loop da data inicial ate a data final
WHILE p_data < $2::DATE
LOOP
IF p_data = $1::DATE AND fn_dia_util(p_data) = 1 THEN
-- calcula o tempo do primeiro dia
p_minutos := p_minutos + (p_saida - CASE WHEN $1::TIME < p_entrada THEN p_entrada WHEN $1::TIME > p_saida THEN p_saida ELSE $1::TIME END)::INTERVAL;
ELSEIF fn_dia_util(p_data) = 1 THEN
-- calcula o tempo de um dia util inteiro
p_minutos := p_minutos + (p_saida - p_entrada)::INTERVAL;
END IF;
p_data := p_data + INTERVAL '1 day';
END LOOP;
-- calcula o tempo do ultimo dia
p_minutos := p_minutos + ($2::TIME - p_entrada)::INTERVAL;
p_dif := p_minutos;
END IF;
RETURN p_dif;
END; $$ LANGUAGE 'plpgsql';
Pra saber quanto tempo levou pra você ter essa resposta:
SELECT * FROM fn_tempo_util('2011-08-05 08:00:00', '2020-01-29 11:50:00');
---
19919:50:00 (horas úteis)
Responder
03/02/2020
Emerson Nascimento
é preciso se atentar ao retorno de extract(day from (dth_status_prog - dth_proc)):
se dth_proc='02/02/2020 17:25:00' e dth_status_prog='03/02/2020 08:10:00' (dias diferentes, porém intervalo menor que 24h)...
se o retorno for 1, utilize:
se o retorno for 0, utilize:
isto posto, imagine que o horário de expediente seja das 08:00 as 18:00. sendo assim, sabemos que o intervalo é das 18:00h de um dia, até as 08:00h do dia seguinte. nesse intervalo temos 14h; em minutos 840min; em segundos 50400seg.
logo deverá substituir 'segundos_no_intervalo' por 50400.
então sua instrução será:
ou:
não utilizo PostGreSQL, mas acredito que seja este o caminho.
se dth_proc='02/02/2020 17:25:00' e dth_status_prog='03/02/2020 08:10:00' (dias diferentes, porém intervalo menor que 24h)...
se o retorno for 1, utilize:
select avg(extract(epoch from (dth_status_prog - dth_proc - (extract(day from (dth_status_prog - dth_proc)) * 'segundos_no_intervalo')))/60) as tempo_med_prog,
se o retorno for 0, utilize:
select avg(extract(epoch from (dth_status_prog - dth_proc - case when extract(day from dth_status_prog) <> extract(day from dth_proc) then ((extract(day from (dth_status_prog - dth_proc))+1) * 'segundos_no_intervalo') else 0 end) )/60) as tempo_med_prog,
isto posto, imagine que o horário de expediente seja das 08:00 as 18:00. sendo assim, sabemos que o intervalo é das 18:00h de um dia, até as 08:00h do dia seguinte. nesse intervalo temos 14h; em minutos 840min; em segundos 50400seg.
logo deverá substituir 'segundos_no_intervalo' por 50400.
então sua instrução será:
select avg(extract(epoch from (dth_status_prog - dth_proc - (extract(day from (dth_status_prog - dth_proc)) * 50400)))/60) as tempo_med_prog,
ou:
select avg(extract(epoch from (dth_status_prog - dth_proc - case when extract(day from dth_status_prog) <> extract(day from dth_proc) then ((extract(day from (dth_status_prog - dth_proc))+1) * 50400) else 0 end) )/60) as tempo_med_prog,
não utilizo PostGreSQL, mas acredito que seja este o caminho.
Responder
Clique aqui para fazer login e interagir na Comunidade :)