Calculo de horas entre duas datas/horas

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Mensagemem Qua, 14 Jun 2006 8:28 am

Olá pessoal, preciso da ajuda de voces feras. Tenho 2 colunas em uma tabela que são do tipo Varchar2. não posso alterar isso porque elas fazem parte do aplicativo. Tenho gravado nestas colunas a data e hora que ocorreram uma certa operação e preciso então fazer o calculo de horas entre estas duas colunas. O fato é que não estou conseguindo formatar a coluna como data para poder fazer este calculo. Os dados estao gravados no seguinte exemplo de formato: 13/06/06 06:26:17

alguém pode me ajudar em como fazer o calculo de horas entre estas 2 colunas

obrigado
Marlon Pasquali
Localização: Erechim - RS

Mensagemem Qua, 14 Jun 2006 3:06 pm

neste exemplo estou fazendo apenas uma subtração:
Código: Selecionar todos
select to_date('02/06/2006 12:30:00','DD/MM/RRRR HH24:MI:SS') - to_date('01/06/2006 11:30:00','DD/MM/RRRR HH24:MI:SS') DIAS
from dual

resultado em Dias:
Código: Selecionar todos
DIAS
----------
1,04166667
1 linha selecionada.


Isso ajuda?..qualquer coisa informe qual tipo de calculo você precisa executar.
TBou
Localização: Campo Grande - MS

Thiago Bourscheidt
thiago.info@apoiorural.com.br
Analista de Sistemas

Mensagemem Qua, 14 Jun 2006 3:14 pm

beleza TBou, peguei o teu exemplo e multipliquei por 24 e obtive o numero de horas. Disso que eu precisava


valeu, obrigado
Marlon Pasquali
Localização: Erechim - RS

Mensagemem Ter, 25 Jul 2006 5:56 pm

OPas...Para montar uma String com o período em Dias, Horas,Minutos que temos entre as duas podemos fazer uma concatenação assim

Código: Selecionar todos
select round(to_number(to_date('02/06/2006 11:00','DD/MM/RRRR HH24:MI') -
                       to_date('01/06/2006 10:30','DD/MM/RRRR HH24:MI')))||' Dia(s) '||
       round(((to_number(to_date('02/06/2006 11:00','DD/MM/RRRR HH24:MI') -
                         to_date('01/06/2006 10:30','DD/MM/RRRR HH24:MI')) * 1440) -1440)/60)||' Hora(s) '||
       round((to_number(to_date('02/06/2006 11:00','DD/MM/RRRR HH24:MI') -
                         to_date('01/06/2006 10:30','DD/MM/RRRR HH24:MI')) * 1440) -1440)||' Minuto(s) '
from dual


Resultado

Código: Selecionar todos
ROUND(TO_NUMBER(TO_DATE('02/06/200611:00','DD/MM/RRRRHH24:MI')-TO_DATE('01/06/ 
--------------------------------------------------------------------------------
1 Dia(s) 0 Hora(s) 30 Minuto(s)                                                 
TBou
Localização: Campo Grande - MS

Thiago Bourscheidt
thiago.info@apoiorural.com.br
Analista de Sistemas

Mensagemem Ter, 25 Jul 2006 6:53 pm

Moçada Descuple pelo Equívoco mas esta Select que eu passei só funciona quando é um Dia entre as datas.

eu corrigi a select e simplifiquei ela acho que agora vai ficar mais simples para entender
vejam:

Código: Selecionar todos
select round(INTERVALO)||' Dia(s) '||
       /* 1440 É O NUMERO DE MINUTOS POR HORA*/                       
       round(((INTERVALO * 1440) -(round(INTERVALO)*1440))/60)||' Hora(s) '||
       round( (INTERVALO * 1440) -(round(INTERVALO)*1440))||' Minuto(s) '
from (select to_number(to_date('02/06/2006 11:00','DD/MM/RRRR HH24:MI') -
                       to_date('01/06/2006 10:30','DD/MM/RRRR HH24:MI')) INTERVALO
        from dual )


valeu Até mais
TBou
Localização: Campo Grande - MS

Thiago Bourscheidt
thiago.info@apoiorural.com.br
Analista de Sistemas

Mensagemem Seg, 25 Fev 2008 11:22 am

Cara,

Este teu select está errado ainda. Se voc colocar um intervalo que seja igual ou ultrapasse 60 minutos ele retorna 60 minutos, 75 minutos, onde deveria somar um na hora e a diferença ser colocada nos minutos. vou corrigir e posto aqui...

Abraço.
gersonjr
Localização: Maceió - AL

Mensagemem Seg, 25 Fev 2008 3:02 pm

eu tenho mais uma duvida pra piorar o caso das horas...

eu preciso fazer a subtração das datas, mais só posso considerar 10horas por dia...

exemplo:
O cara enviou um e-mail para mim ontem as 03:00 da tarde
e eu respondi para ele hj as 03:00 da tarde...
e eu preciso de saber quanto tempo, em horas uteis, eu demorei para responder.... (horas uteis = 08:00 da manha até 18:00 da noite)

isso parece fácil, mais é qui eu tenho mais de 150 e-mail para fazer isso =D
então se alguém pudesse me ajudar com alguma formula para isso =D

Grato...
gokden
Localização: Ribeirão Preto - SP

Lucas de Souza

OCA Developer
Analista de sistemas

Mensagemem Seg, 25 Fev 2008 5:20 pm

Lucas, acho que no seu caso, você vai precisar de uma tabela indicando os sábados, domingos e feriados, e uma função que retorne a diferença em horas úteis entre duas datas. A função deverá varrer um loop a partir da primeira data até a última, somando as horas úteis de cada dia no loop.

Ex: considere d1 e d2 as datas inicial e final.

-Se d1 e d2 forem do mesmo dia, o número de horas úteis será a diferença entre d1 e d2, ou 0 se d1 e d2 caírem num feriado, sábado ou domingo. Lembre-se de analisar o que fazer se d1 for menor que 08:00 ou d2 for maior que 18:00. Eu consideraria como 0.

-Se d2 for maior que d1, a diferença será:
O número de horas úteis de d1 até as 18:00+
10*o número de dias entre d1 e d2 (ou 0 se d2-d1 for menor que 24h)+
o número de horas úteis de 08:00 até d2.


Agora, é só criar um pacote e escrever uma função que faça isso!!
Ricardo Carmo
Localização: Maceió-AL

Mensagemem Seg, 25 Fev 2008 8:37 pm

Já que eu estava meio sem ter o que fazer.. apenas a título de curiosidade, vai aí uma solução usando somente SQL:

Código: Selecionar todos
create table tab_email
( id_email        number
, dat_recebimento date
, dat_resposta    date
);

ID_EMAIL  DAT_RECEBIMENTO     DAT_RESPOSTA
1          01/02/2008 13:25:31 09/02/2008 04:19:12
2          07/02/2008 13:25:31 22/02/2008 05:02:24
3          05/02/2008 13:25:31 18/02/2008 11:45:36
4   25/02/2008 08:00:00 26/02/2008 12:00:00


Código: Selecionar todos
-- Obs.: Estarei assumindo que você possui uma tabela de feriados
-- chamada tab_feriados, onde estão cadastrados a data
-- truncada de cada feriado
select -- Agora ele serve para algo...
       y.id_email
       -- Calcula o número de horas entre os intervalos inicial e final
       -- Lembrando que, se o e-mail foi enviado ou recebido em um fim
       -- de semana, feriado, ou fora do expediente normal, estas horas
       -- não serão contabilizadas.
     , trunc(sum(y.intervalo_fim - y.intervalo_ini))||' dias '||
       to_char(to_date('00010101', 'yyyymmdd') + sum(y.intervalo_fim - y.intervalo_ini), 'hh24:mi:ss')
       tempo_total
from
(
  select -- A chave da tabela de e-mails ainda está aqui...
         z.id_email
         -- Intervalo anterior retornado em "z"
       , lag (z.intervalo, 1, z.dat_recebimento) over(partition by z.id_email order by z.intervalo) intervalo_ini
         -- Intervalo retornado em "z", exceto para a última linha.
         -- Neste caso, retornamos a própria data de resposta
         -- (por isso o intervalo arredondava a hora de resposta para cima
         -- gerando uma linha a mais que é aproveitada aqui)
       , decode( lead(z.intervalo) over(partition by z.id_email order by z.intervalo)
               , null, dat_resposta
               , intervalo
               ) intervalo_fim
  from
  (
    select -- Código do e-mail (chave da tabela) para juntar a bagunça toda...
           e2.id_email                                                 
           -- Cada linha irá retornar uma hora após a data de recebimento
           -- do em-mail até a hora posterior à resposta do e-mail
           -- (não estaremos considerando os minutos e segundos)
         , trunc(e2.dat_recebimento + (incremento / 24), 'hh') intervalo
           -- Para uso futuro =)
         , e2.dat_resposta
         , e2.dat_recebimento
    from   tab_email e2
         , ( -- Esta query irá gerar uma seqüência que será somada à hora de
             -- recebimento do e-mail para separarmos cada linha de tab_email
             -- em intervalos de 1 hora.
             select level-1 incremento
             from   dual
             connect by level <= ( -- Recuperar o maior intervalo de datas na tabela,
                                   -- para somar à data inicial e conseqüentemente
                                   -- quebrarmos a data em intervalos de 1 hora.
                                   select max(dat_resposta-dat_recebimento)
                                   from   tab_email
                                 ) * 24 -- 24 horas em um dia...
           ) d
    -- Não considerar intervalos inferiores à data de recebimento
    where  e2.dat_recebimento <= trunc(e2.dat_recebimento + (incremento     / 24), 'hh')
    -- Não considerar intervalos após a data de resposta (arredondada para cima)
    and    e2.dat_resposta    >= trunc(e2.dat_recebimento + ((incremento-1) / 24), 'hh')
  ) z
) y
where  to_char(y.intervalo_ini, 'D')    not in (1,7)     -- Não contar Sábados e Domingos
and    to_char(y.intervalo_ini, 'hh24') between 8 and 18 -- Apenas das 8:00 às 18:00
/*                                                       -- Não considerar feriados
and    trunc(y.intervalo_ini) not in
       ( select dat_feriado
         from   tab_feriados
       )
*/
and    to_char(y.intervalo_fim, 'D')    not in (1,7)     -- Não contar Sábados e Domingos
and    to_char(y.intervalo_fim, 'hh24') between 8 and 18 -- Apenas das 8:00 às 18:00
/*                                                       -- Não considerar feriados
and    trunc(y.intervalo_fim) not in
       ( select dat_feriado
         from   tab_feriados
       )
*/
group by y.id_email

rogenaro
Localização: Londrina - PR

Rafael O. Genaro

Mensagemem Ter, 26 Fev 2008 4:07 pm

:-o :-o
Oia cara.... essa foi boa eim =D
vou testar esse comando, é qui tem qui arruma o nome dos campo e tals...

mais vlww..

Eu pensei em várias possibilidades também
e a mais simples foi essa:

Código: Selecionar todos
round(Di - Df) = x;

x * (18 - 8) = y;

Hi - 8   = k;
18 - Hf = j;

y - (k + j) = z;

z = Horas uteis =D


ficaria assim:
Código: Selecionar todos
round(Di - Df) = 2; -- Diferença de dias

2 * 10:00 = 20:00; -- Total de dias * horas uteis

09:00 - 08:00 = 01:00; -- Horas Não trabalhadas
18:00 - 12:00 = 06:00; -- Horas Não trabalhadas

20:00 - (01:00 + 06:00) => 20:00 - 07:00 = 13:00 -- Horas Trabalhadas



Mais desse jeito so funcionaria se
Hf > Hi e
Hf e Hi estejam entre as 08:00 e 18:00 hrs

hehehehehe
gokden
Localização: Ribeirão Preto - SP

Lucas de Souza

OCA Developer
Analista de sistemas

Mensagemem Qui, 13 Mar 2008 11:02 am

Olá,

Sou nova no fórum... rsrs.
Estava com problemas com cáculo entre datas e este tópico me ajudou muito.
Necessito encontrar o intervalo entre o primeiro atendimento urgência (FA) o segundo o de internação, ou seja, quanto tempo o paciente fica em observação até ser internado.
O problema é que tenho dois campos um armazena só a data (dt_atendimento) o outro data e hora (hr_atendimento), meu campo hr_atendimento só traz o horário correto a data geralmente difere da dt_atendimento.
Após ler o tópico consegui desenvolver código que traz o intervalo, porém, em alguns casos ele aparece negativo, devido a virada do dia e realmente não sei como alterar isso.

Alguém saberia?

Grata,
Daniela
Localização: São Paulo

Daniela Nunes
"Entrega teu caminho ao Senhor, confia Nele e Ele tudo fará". Salmo 37:5

Mensagemem Qui, 13 Mar 2008 11:14 am

olá´, você pode postar o seu select aqui para nos vermos no que podemos ajudar..?
TBou
Localização: Campo Grande - MS

Thiago Bourscheidt
thiago.info@apoiorural.com.br
Analista de Sistemas

Mensagemem Qui, 13 Mar 2008 11:53 am

Estou tentando concatenar o campo dt_atendimento com hr_atendimento pra depois fazer o cáculo para encontrar o intervalo...
Mas, esta dando erro de formato inválido.

Código: Selecionar todos
Select ds_servico,
round(( (m_intervalo) * 86400 / 3600)) ||':' ||
round(mod( (m_intervalo) * 86400 , 3600 ) / 60 ) || ':'||
round(mod ( mod ( (m_intervalo) * 86400, 3600 ), 60 ))Intervalo_1
from (       
select  ds_servico, (intervalo/qtd_paciente)m_intervalo
from(
Select servico.ds_servico
,count (paciente.cd_paciente)qtd_paciente
,sum(to_number(to_date(interna.hr_atendimento,'HH24:MI')- to_date(urgen.hr_atendimento,'HH24:MI')))intervalo 
from  dbamv.atendime,
dbamv.paciente,
dbamv.servico,
( select dt_atendimento,
     to_char(hr_atendimento,'hh24:mm')hr_atendimento,
        cd_paciente,
        cd_atendimento,
        dt_alta,cd_servico
        from  dbamv.atendime
        where atendime.tp_atendimento='I'
        and   atendime.dt_atendimento between to_date('&dtini','dd/mm/yyyy')and to_date ('&dtfin','dd/mm/yyyy')) interna,
( select dt_atendimento,
        to_char(hr_atendimento,'hh24:mm')hr_atendimento,
         cd_paciente,
         cd_atendimento,
         dt_alta,cd_servico
        from dbamv.atendime
        where atendime.dt_atendimento between to_date('&dtini','dd/mm/yyyy')and to_date ('&dtfin','dd/mm/yyyy')
        and  atendime.tp_atendimento='U') urgen
where atendime.cd_paciente=paciente.cd_paciente
and   paciente.cd_paciente=urgen.cd_paciente
and   paciente.cd_paciente=interna.cd_paciente
and   atendime.cd_servico=urgen.cd_servico
and   atendime.cd_servico=interna.cd_servico
and   atendime.cd_servico=servico.cd_servico
group by ds_servico
         ) 
          group by ds_servico, (intervalo/qtd_paciente)
)
group by ds_servico,M_INTERVALO
Daniela
Localização: São Paulo

Daniela Nunes
"Entrega teu caminho ao Senhor, confia Nele e Ele tudo fará". Salmo 37:5

Mensagemem Qui, 13 Mar 2008 7:02 pm

Daniela, pelo que você disse, o problema está no fato de que você está considerando apenas a o campo hr_atendimento.
O campo dt_atendimento é do tipo DATE e guarda a data do atendimento, enquanto que o campo hr_atendimento também é do tipo DATE, mas guarda apenas o horário do atendimento, certo?
Aparentemente tanto as horas no campo dt_atendimento quanto o dia no campo hr_atendimento não são gravados corretamente em 100% dos casos, sendo necessário obter a informação completa pelos dois campos. É isso mesmo?

Caso eu tenha entendido corretamente, tente as alterações abaixo:
Código: Selecionar todos
select ds_servico
     , round(((m_intervalo) * 86400 / 3600)) || ':'
     ||round( mod( (m_intervalo) * 86400
                , 3600
                ) / 60
            )
     || ':'
     ||round(mod( mod( (m_intervalo) * 86400
                     , 3600
                     )
                , 60
                )
            ) intervalo_1
from   
(
  select ds_servico
       , (intervalo / qtd_paciente) m_intervalo
  from   
  (
   select servico.ds_servico
        , count(paciente.cd_paciente)   qtd_paciente
        -- Alteração 1
/*
        , sum(to_number( to_date(interna.hr_atendimento,'HH24:MI')
                       - to_date(urgen.hr_atendimento  ,'HH24:MI')
                       )
             ) intervalo
*/
        , sum( interna.hr_atendimento
             - urgen.hr_atendimento
             ) intervalo
        -- Fim da alteração 1
   from   dbamv.atendime
        , dbamv.paciente
        , dbamv.servico
        , (select dt_atendimento
                 -- Alteração 2
/*                , to_char( hr_atendimento
                         , 'hh24:mm'
                         ) hr_atendimento
*/                       
                , to_date( to_char(dt_atendimento, 'yyyymmdd')
                         ||to_char(hr_atendimento, 'hh24miss')
                         , 'yyyymmddhh24miss'
                         )
                -- Fim da alteração 2
                , cd_paciente
                , cd_atendimento
                , dt_alta
                , cd_servico
           from   dbamv.atendime
           where  atendime.tp_atendimento = 'I'
           and    atendime.dt_atendimento between to_date('&dtini','dd/mm/yyyy')
                                          and     to_date('&dtfin','dd/mm/yyyy')
          ) interna
        , (select dt_atendimento
                 -- Alteração 3
/*                , to_char( hr_atendimento
                         , 'hh24:mm'
                         ) hr_atendimento
*/                       
                , to_date( to_char(dt_atendimento, 'yyyymmdd')
                         ||to_char(hr_atendimento, 'hh24miss')
                         , 'yyyymmddhh24miss'
                         )
                -- Fim da alteração 3
                , cd_paciente
                , cd_atendimento
                , dt_alta
                , cd_servico
           from   dbamv.atendime
           where  atendime.dt_atendimento between to_date('&dtini','dd/mm/yyyy')
                                          and     to_date('&dtfin','dd/mm/yyyy')
           and    atendime.tp_atendimento = 'U'
          ) urgen
   where  atendime.cd_paciente = paciente.cd_paciente
   and    paciente.cd_paciente = urgen.cd_paciente
   and    paciente.cd_paciente = interna.cd_paciente
   and    atendime.cd_servico = urgen.cd_servico
   and    atendime.cd_servico = interna.cd_servico
   and    atendime.cd_servico = servico.cd_servico
   group by ds_servico
  )
  group by ds_servico
         , (intervalo / qtd_paciente)
)
group by ds_servico
        , m_intervalo

rogenaro
Localização: Londrina - PR

Rafael O. Genaro

Mensagemem Sex, 14 Mar 2008 9:07 am

Olá Rafael,

Com as alterações, passamos a trabalhar com data e hora, perfeito...
Valeu pela força, não estava conseguindo fazer essa junção.
Porém, algumas linhas ainda me apresentam resultado de horas negativas , e esse é o maior problema. Tentei jogar as regras de sinais e modificar o formato da data , mas, não tive sucesso...
Daniela
Localização: São Paulo

Daniela Nunes
"Entrega teu caminho ao Senhor, confia Nele e Ele tudo fará". Salmo 37:5

Mensagemem Sex, 14 Mar 2008 6:34 pm

Ops... Imaginei que, considerando também a data no momento de subtrair os resultados, o problema das horas negativas iria sumir (pois poderia, em teoria, ocorrer de um atendimento iniciar às 23:30 e terminar à 00:15 do dia seguinte, por exemplo).

Como não resolveu o problema, é provável que por algum motivo a linha
Código: Selecionar todos
        , sum( interna.hr_atendimento
             - urgen.hr_atendimento
             ) intervalo


esteja causando o problema devido ao horário de atendimento de "urgen" ser maior do que a data e hora retornada em "interna.hr_atendimento"

Para verificar, tente realizar a consulta mais interna e verifique se existe algum problema (seja nos dados da tabela, seja em algum ponto da consulta) que esteja retornando registros com o campo urgen.hr_atendimento maior do que interna.hr_atendimento.

Qualquer coisa, se for necessário (e possível, claro), coloque a estrutura das tabelas envolvidas com alguns dados e o resultado esperado, para ajudar nos testes.[/u]
rogenaro
Localização: Londrina - PR

Rafael O. Genaro

Mensagemem Seg, 17 Mar 2008 8:17 am

Olá Rafael,

Bom dia, o problema do resultado negativo era o seguinte:
Um paciente pode ter 1 ou mais atendimentos de urgência antes de uma internação e ainda ter mais de uma internação, as condições da consulta traziam todos as urgências e comparavam com a data e hora da internação.

Aí acrecentei a linha "and urgen.dt_alta=interna.dt_atendimento" na clausula Where fazendo com que apenas o atendimento de urgência antes da internação (caso houvesse) fosse subtraído.

:D

Valeu.... boa semana para você...
Daniela
Localização: São Paulo

Daniela Nunes
"Entrega teu caminho ao Senhor, confia Nele e Ele tudo fará". Salmo 37:5

Mensagemem Ter, 18 Mar 2008 1:01 pm

Gente, para de querer complicar as coisas usando procedure pra tudo....
usem select e funões....


SELECT TO_CHAR(SYSDATE + INTERVAL '10' MINUTE, 'HH:MI:SS')
FROM dual;

Nem li todos os posts...mas parece que todas as soluções estão muito complicadas pra ter a diferença de horas de datas....
esse é um caminho....se fuçarem mais isso pode ser resolvido somente em um select.
ruevers
Localização: sp


Mensagemem Sex, 08 Jan 2010 8:30 am

Estava tentando fazer mas estou com um breve problema, está indicando um erro, o select é esse:

SELECT TO_CHAR (datahorainicio + INTERVAL tempo MINUTE, 'HH24:mi:ss')
FROM (SELECT datahorainicio,
TO_CHAR (ROUND (((datahorafim - datahorainicio) * 1440))) AS tempo
FROM atendimento);

com esse erro >> "ORA-00907: missing right parenthesis"
mateustads
Localização: MS

Mateus Leonardi

Mensagemem Sex, 08 Jan 2010 8:37 am

Código: Selecionar todos
SELECT TO_CHAR ( (datahorainicio + INTERVAL || ''' ||tempo || ''' || MINUTE), 'HH24:mi:ss')
FROM
(SELECT datahorainicio,
        TO_CHAR ( ROUND (   (   (datahorafim - datahorainicio) * 1440)  )) AS tempo
FROM atendimento);
victorhugomuniz
Localização: Rio de Janeiro - RJ

Jesus está voltando, volte antes para Ele.

Imagem JavaBlackBelt


  • Veja também
    Respostas
    ExibiÇões
    Última mensagem

          Próximo

          Voltar para SQL

          Quem está online

          Usuários navegando neste fórum: Nenhum usuário registrado e 3 visitantes