DESAFIO - PostgreSQL - Cálculo de Horas Úteis

PostgreSQL

05/08/2011

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!
Marcelo Nascimento

Marcelo Nascimento

Curtidas 0

Respostas

Caio Cruz

Caio Cruz

05/08/2011

Olá Marcelo, pesquisei ontem sobre o mesmo assunto e não encontrei nada além desse post, então fiz uma function pra isso:


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)
GOSTEI 0
Emerson Nascimento

Emerson Nascimento

05/08/2011

é 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:
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.
GOSTEI 0
POSTAR