Média em tempo (Horas,minutos e segundos)

[Motta 645]

Outro dia um amigo me pediu uma solução para calcular uma média envolvendo tempo, a melhor solução que vi foi calcular em segundos pois é a medida mais "baixa" em DATETIME e depois converter para horas,minutos e segundos

 

Segue o exemplo básico que fiz, pode ser útil

ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI:SS'

--UMA BASE FAJUTA
select DTDE,DTATE
from
(
select TO_DATE('13/06/2013 01:01:01','DD/MM/YYYY HH24:MI:SS') DTDE,TO_DATE('14/06/2013 01:02:01','DD/MM/YYYY HH24:MI:SS') DTATE from DUAL
union all
select TO_DATE('13/06/2013 02:01:01','DD/MM/YYYY HH24:MI:SS') DTDE,TO_DATE('13/06/2013 02:02:01','DD/MM/YYYY HH24:MI:SS') DTATE from DUAL
union all
select TO_DATE('13/06/2013 13:34:34','DD/MM/YYYY HH24:MI:SS') DTDE,TO_DATE('13/06/2013 15:02:01','DD/MM/YYYY HH24:MI:SS') DTATE from DUAL
union all
select TO_DATE('13/06/2013 09:34:34','DD/MM/YYYY HH24:MI:SS') DTDE,TO_DATE('13/06/2013 09:56:56','DD/MM/YYYY HH24:MI:SS') DTATE from DUAL
)

/*
DTDE                      DTATE
------------------------- -------------------------
13/06/2013 01:01:01       14/06/2013 01:02:01
13/06/2013 02:01:01       13/06/2013 02:02:01
13/06/2013 13:34:34       13/06/2013 15:02:01
13/06/2013 09:34:34       13/06/2013 09:56:56

*/

--DIFERENCA ENTRE DATAS
select (DTATE-DTDE)
from
(
select TO_DATE('13/06/2013 01:01:01','DD/MM/YYYY HH24:MI:SS') DTDE,TO_DATE('14/06/2013 01:02:01','DD/MM/YYYY HH24:MI:SS') DTATE from DUAL
union all
select TO_DATE('13/06/2013 02:01:01','DD/MM/YYYY HH24:MI:SS') DTDE,TO_DATE('13/06/2013 02:02:01','DD/MM/YYYY HH24:MI:SS') DTATE from DUAL
union all
select TO_DATE('13/06/2013 13:34:34','DD/MM/YYYY HH24:MI:SS') DTDE,TO_DATE('13/06/2013 15:02:01','DD/MM/YYYY HH24:MI:SS') DTATE from DUAL
union all
select TO_DATE('13/06/2013 09:34:34','DD/MM/YYYY HH24:MI:SS') DTDE,TO_DATE('13/06/2013 09:56:56','DD/MM/YYYY HH24:MI:SS') DTATE from DUAL
)

/*
(DTATE-DTDE)
----------------------
1,00069444444444444444444444444444444444
0,000694444444444444444444444444444444444444
0,0607291666666666666666666666666666666667
0,0155324074074074074074074074074074074074
*/

--EM SEGUNDOS
select 24*60*60*(DTATE-DTDE) X
from
(
select TO_DATE('13/06/2013 01:01:01','DD/MM/YYYY HH24:MI:SS') DTDE,TO_DATE('14/06/2013 01:02:01','DD/MM/YYYY HH24:MI:SS') DTATE from DUAL
union all
select TO_DATE('13/06/2013 02:01:01','DD/MM/YYYY HH24:MI:SS') DTDE,TO_DATE('13/06/2013 02:02:01','DD/MM/YYYY HH24:MI:SS') DTATE from DUAL
union all
select TO_DATE('13/06/2013 13:34:34','DD/MM/YYYY HH24:MI:SS') DTDE,TO_DATE('13/06/2013 15:02:01','DD/MM/YYYY HH24:MI:SS') DTATE from DUAL
union all
select TO_DATE('13/06/2013 09:34:34','DD/MM/YYYY HH24:MI:SS') DTDE,TO_DATE('13/06/2013 09:56:56','DD/MM/YYYY HH24:MI:SS') DTATE from DUAL
)

/*
X
----------------------
86459,9999999999999999999999999999999996
59,99999999999999999999999999999999999996
5247,000000000000000000000000000000000003
1341,999999999999999999999999999999999999

*/

--MEDIA
select  AVG(X)
from
(
select 24*60*60*(DTATE-DTDE) X
from
(
select TO_DATE('13/06/2013 01:01:01','DD/MM/YYYY HH24:MI:SS') DTDE,TO_DATE('14/06/2013 01:02:01','DD/MM/YYYY HH24:MI:SS') DTATE from DUAL
union all
select TO_DATE('13/06/2013 02:01:01','DD/MM/YYYY HH24:MI:SS') DTDE,TO_DATE('13/06/2013 02:02:01','DD/MM/YYYY HH24:MI:SS') DTATE from DUAL
union all
select TO_DATE('13/06/2013 13:34:34','DD/MM/YYYY HH24:MI:SS') DTDE,TO_DATE('13/06/2013 15:02:01','DD/MM/YYYY HH24:MI:SS') DTATE from DUAL
union all
select TO_DATE('13/06/2013 09:34:34','DD/MM/YYYY HH24:MI:SS') DTDE,TO_DATE('13/06/2013 09:56:56','DD/MM/YYYY HH24:MI:SS') DTATE from DUAL
)
)

/*
AVG(X)
----------------------
23277,2499999999999999999999999999999999
*/

--MEDIA EM DIAS
select  (AVG(X))/(24*60*60) S
from
(
select 24*60*60*(DTATE-DTDE) X
from
(
select TO_DATE('13/06/2013 01:01:01','DD/MM/YYYY HH24:MI:SS') DTDE,TO_DATE('14/06/2013 01:02:01','DD/MM/YYYY HH24:MI:SS') DTATE from DUAL
union all
select TO_DATE('13/06/2013 02:01:01','DD/MM/YYYY HH24:MI:SS') DTDE,TO_DATE('13/06/2013 02:02:01','DD/MM/YYYY HH24:MI:SS') DTATE from DUAL
union all
select TO_DATE('13/06/2013 13:34:34','DD/MM/YYYY HH24:MI:SS') DTDE,TO_DATE('13/06/2013 15:02:01','DD/MM/YYYY HH24:MI:SS') DTATE from DUAL
union all
select TO_DATE('13/06/2013 09:34:34','DD/MM/YYYY HH24:MI:SS') DTDE,TO_DATE('13/06/2013 09:56:56','DD/MM/YYYY HH24:MI:SS') DTATE from DUAL
)
)

/*
S
----------------------
0,2694126157407407407407407407407407407396
*/


--FORCA UM DIA ZERADO E SOMA ESTES DIAS
select  TO_DATE('01/01/2013 00:00:00','DD/MM/YYYY HH24:MI:SS') + (AVG(X))/(24*60*60) S
from
(
select 24*60*60*(DTATE-DTDE) X
from
(
select TO_DATE('13/06/2013 01:01:01','DD/MM/YYYY HH24:MI:SS') DTDE,TO_DATE('14/06/2013 01:02:01','DD/MM/YYYY HH24:MI:SS') DTATE from DUAL
union all
select TO_DATE('13/06/2013 02:01:01','DD/MM/YYYY HH24:MI:SS') DTDE,TO_DATE('13/06/2013 02:02:01','DD/MM/YYYY HH24:MI:SS') DTATE from DUAL
union all
select TO_DATE('13/06/2013 13:34:34','DD/MM/YYYY HH24:MI:SS') DTDE,TO_DATE('13/06/2013 15:02:01','DD/MM/YYYY HH24:MI:SS') DTATE from DUAL
union all
select TO_DATE('13/06/2013 09:34:34','DD/MM/YYYY HH24:MI:SS') DTDE,TO_DATE('13/06/2013 09:56:56','DD/MM/YYYY HH24:MI:SS') DTATE from DUAL
)
)
/*
S
-------------------------
01/01/2013 06:27:57
*/

--CONVERTANDO EM HORA
select  TO_CHAR(TO_DATE('01/01/2013 00:00:00','DD/MM/YYYY HH24:MI:SS') + (AVG(X))/(24*60*60),'HH24:MI:SS') S
from
(
select 24*60*60*(DTATE-DTDE) X
from
(
select TO_DATE('13/06/2013 01:01:01','DD/MM/YYYY HH24:MI:SS') DTDE,TO_DATE('14/06/2013 01:02:01','DD/MM/YYYY HH24:MI:SS') DTATE from DUAL
union all
select TO_DATE('13/06/2013 02:01:01','DD/MM/YYYY HH24:MI:SS') DTDE,TO_DATE('13/06/2013 02:02:01','DD/MM/YYYY HH24:MI:SS') DTATE from DUAL
union all
select TO_DATE('13/06/2013 13:34:34','DD/MM/YYYY HH24:MI:SS') DTDE,TO_DATE('13/06/2013 15:02:01','DD/MM/YYYY HH24:MI:SS') DTATE from DUAL
union all
select TO_DATE('13/06/2013 09:34:34','DD/MM/YYYY HH24:MI:SS') DTDE,TO_DATE('13/06/2013 09:56:56','DD/MM/YYYY HH24:MI:SS') DTATE from DUAL
)
)

/*
S
-----------
06:27:57
*/

Este exemplo se limita a médias inferiores à um dia, mas isto pode ser facilmente resolvido, para o obter o dia é subtrair 1, pois o artifício parte do dia "1".