Calculation of hours between two dates / times

[Marlon Pasquali]

Hello everyone, I need the help of you beasts. I have 2 columns in a table that are of the varchar2 type. I can not change this because they are part of the application. I have recorded in these columns the date and time that occurred a certain operation and I need to do the calculation of hours between these two columns. The fact is that I can not format the column as date to be able to do this calculation. The data are recorded in the following format example: 13/06/06 06:26:17

Someone can help me with how to calculate hours between these 2 columns

Thanks

[TBou]

In this example I am only making a subtraction:

Select all

 
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 

Results on days:

Select all

 
DIAS 
---------- 
1,04166667 
1 linha selecionada. 

Does this help? .. Anything tells which type of calculation you need to run.

[Marlon Pasquali]

beleza tbou, I got your example and multiplied by 24 and I got the number of hours. That I needed


thanks, thank you

[TBou]

PAHO ... to set up a string with the period in days, hours, minutes we have between the two we can make a concatenation thus

Select all

 
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  

1]]

[TBou]

Muddy decuple by misconception but this select I passed only works when it's a day between the dates.

I fixed the select and I simplified it I think now will be simpler to understand
:

valeu more

[gersonjr]

Dude,

This select is wrong yet. If you put a range that is equal or surpassing 60 minutes it returns 60 minutes, 75 minutes, where you should add one on time and the difference is placed in minutes. I will correct and post here ...

Embrace.

[gokden]

I have another doubt to make matters worse ...

I need to do the subtraction of dates, but só I can consider 10 hours a day ...

Example:
The guy sent me an email yesterday at 3:00 pm
and I answered him hj at 03:00 in the afternoon ...
And I need to know how long, in useful hours, I took time to answer .... (useful hours = 08:00 in the morning until 18:00 at night)

This seems easy, more is chi I have more than 150 e-mail to do this = D
então if someone could help me with some formula for this = D

grateful ...

[Ricardo Carmo]

Lucas, I think in your case, you will need a table indicating Saturdays, Sundays and holidays, and a function that returns the difference in working hours between two dates. The function should sweep a loop from the first date to the last, adding the working hours of each day in the loop.

Ex: Consider D1 and D2 the initial and final dates.

- If D1 and D2 are the same day, the number of hours useful will be the difference between D1 and D2, or 0 if D1 and D2 fall on a holiday, Saturday or Sunday. Remember to analyze what to do if D1 is less than 08:00 or D2 is greater than 18:00. I would consider as 0.

- If d2 is greater than D1, the difference will be:
the number of hours of D1 until 18:00
10 * The number of days between D1 and D2 (or 0 if D2-D1 is less than 24h) +
the number of hours of 08:00 to D2.


Now, just create a package and write a function that does it !!

[rogenaro]

Since I was kind of without having to do .. Just as curiosity, there is a solution using only SQL:

Select all

 
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 

Select all

 
-- 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 
 

[gokden]

: -The: -The
Oia guy .... this was good eim = D
I will test this command, it is chi to get the name of the field and tals ... [[99 ]
More vlww ..

I thought of several possibilities também
and the simplest was this:

Select all

 
round(Di - Df) = x; 
 
x * (18 - 8) = y; 
 
Hi - 8   = k; 
18 - Hf = j; 
 
y - (k + j) = z; 
 
z = Horas uteis =D 

It would be like this:

Select all

 
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 
 

More in this way it would work if
HF> HI and
HF and HI are between 08:00 and 18:00 HRS

Hehehehehe

[Daniela]

Hello,

I'm new to the forum ... rsrs.
I was having problems with a chair between dates and this topic helped me a lot.
I need to find the interval between the first urgency service (AF) the second of hospitalization, that is, how long the patient is observed until he is hospitalized.
The problem is that I have two fields a stores only the date (DT_NING) the other date and time (HR_ATING), my HR_ATING field only brings the correct time the date generally differs from DT_NING.
After reading the topic I managed to develop code that brings the interval, however, in some cases it appears negative due to the turn of the day and I really do not know how to change it.

Would anyone know?

Grata,

[TBou]

Hello, can you post your select here to see us in what we can help ..?

[Daniela]

I am trying to concatenate the DT_-sensation field with HR_NING to then make the Chair to find the interval ...
But, it is giving invalid format error.

Select all

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

[rogenaro]

Daniela, from what you said, the problem is in the fact that you are considering only the HR_Ancia field.
The DT_NING field is DATE type and keeps the date of the service, while the HR_All field is also the date type, but you guard only the service schedule, right?
Apparently both hours in the DT_NING field and the day in the HR_Anmark field are not correctly recorded in 100% of the cases, it is necessary to obtain complete information by the two fields. That's right?

If I have understood correctly, try the following changes:

Select all

 
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 
 

[Daniela]

Hello Rafael,

With the changes, we spent working with date and time, perfect ...
Thanks for force, I was not able to do this junction.
However, some lines still have me result of negative hours, and this is the biggest problem. I tried to play the rules of signs and modify the format of the date, but, I was not successful ...

[rogenaro]

Ops ... I imagined that, also considering the date at the time of subtracting the results, the problem of the negative hours would disappear (because it could, in theory [/ u], occur from a service start at 23:30 and finish at 00:15 the next day, for example).

As has not solved the problem, it is likely that for some reason the line

Select all

 
        , sum( interna.hr_atendimento 
             - urgen.hr_atendimento 
             ) intervalo 

is causing the problem due to the time of care of "urgen" to be that the date and time returned in "internal.hr_ying"

to check, try to perform the internal query and verify that there is any problem (whether in the table data, either at some point of the query ) that is returning records with the URGEN.HR_All field larger than internal.hr_nationalness.

Anything, if necessary (and possible, of course), place the structure of the tables involved with some data and the expected result, to help in the tests. [/ U]

[Daniela]

Hello Rafael,

Good morning, the problem of negative result was as follows:
One patient can have 1 or more urgent care before hospitalization and still have more than one Hospitalization, the conditions of the consultation brought all the urgencies and compared with the date and time of hospitalization.
.

: D

Thanks .... Good week for you ...

[ruevers]

People, stop wanting to complicate things using procedure for everything ....
use select and functions ....

Select all

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

I read all the posts ... but it seems that all solutions are very complicated to have the difference of date hours ....
This is a path .... if they miss this can be solved only in A SELECT.

[mateustads]

I was trying to do but I have a brief problem, it is indicating an error, the SELECT is this:

Select all

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

With this error:

Select all

 "ORA-00907: missing right parenthesis"

[victorhugomuniz]

Select all

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

[mateustads]

It did not work for your scheme ... = x

In the end I ended up having a fuction for this ... short and functional ... take a look.

Select all

CREATE OR REPLACE function SEMASA.f_tempo_media ( day_fraction NUMBER) 
return char 
is 
 
 Years        NUMBER; 
 months       NUMBER; 
 days         NUMBER; 
 hrs          NUMBER; 
 mints        NUMBER; 
 sec          NUMBER; 
 
begin 
 
 hrs   :=trunc(day_fraction*24); 
 mints :=trunc((((day_fraction)*24)-(hrs))*60); 
 sec   :=trunc(mod((day_fraction)*86400,60)); 
 
 return(LPAD (hrs, 2, '0') ||':'|| LPAD (mints, 2, '0') ||':'|| LPAD (sec, 2, '0')); 
 
end; 
/

[huntersc]

Dear,

I was seeing the previous Selects and I could not find a result for mine.

I need to find a vague schedule to schedule care.
My application There is a table where you have scheduled schedules.
Date / Time Home (DHPREVIST) Date / Time End Tempprivisto.

Ex: I have scheduled scheduled:
08:00 - 09:00 11:00 - 12:00
I need the select to return the schedule that has vague From 10 the 11
all this taking into account hours from 8 to 12 from 13:30 at 18:00 only during weekday and limit between 60-minute schedules.

Thank you for collaboration.

Thanks.

[priajf]

Ok
I got this code

Select all

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

But now I need to do a search with schedules that passed from 12 min. How do I do. Time does not do it on the table.

[SergioLBJr]

You better explain your situation.

You have a table with initial and final time and want to see what went from 12 min ??

[Victor_Ni]

Greetings. There are times when there are many technicians with the same question, " How to calculate the time between two dates? ", only for recapitulate. It is not a question of calculating the time between a date and the current date (Sysdate), as it would be simple using the Oracle Timestamp function.
Not knowing more details, very s ask: To know the time elapsed between dates?
There are several reasons. Even more for a DBA, process monitoring would be the most appropriate response to this issue.

Now in practice, did Aguem accurately create a script that works 100%?

This is not a challenge, I just do not want to be repetitive in solution options.

Hugs.

[spernega]

Good morning Victor,

has a function that I created and that is working well.
At least so far I have not taken any mistake.

Select all

create or replace  
function Fn_Intervalo_Tempo (P_Dt_Inic in date, 
                             P_Dt_Fina in date) return varchar2 is 
 v_inte  number; 
 v_Hora  varchar2(200); 
 -- 
 cursor c is 
   SELECT lpad(   extract (day    from numtodsinterval(v_inte, 'day'))    ,5,' ') Qt_Dias, 
          lpad(   extract (hour   from numtodsinterval(v_inte, 'day'))    ,2,'0') Qt_Horas, 
          lpad(   extract (minute from numtodsinterval(v_inte, 'day' ))   ,2,'0') Qt_Minutos, 
          lpad(   extract (second from numtodsinterval(v_inte, 'day'))    ,2,'0') Qt_Segundos 
   FROM   dual; 
 c_r c%rowtype; 
 -- 
begin 
 -- 
 v_inte := trunc((p_dt_Fina - p_dt_Inic),6); 
 dbms_output.put_line('v_inte     '||to_char(v_inte)); 
 -- 
 open c; 
  fetch c into c_r; 
   if c%found then 
      -- 
      dbms_output.put_line('c_r.Qt_Dias     '||c_r.Qt_Dias); 
      dbms_output.put_line('c_r.Qt_Horas    '||c_r.Qt_Horas); 
      dbms_output.put_line('c_r.Qt_Minutos  '||c_r.Qt_Minutos); 
      dbms_output.put_line('c_r.Qt_Segundos '||lpad(trunc(c_r.Qt_Segundos),2,'0')); 
      -- 
      v_hora := c_r.Qt_Dias||' '||c_r.Qt_Horas||':'||c_r.Qt_Minutos||':'||lpad(trunc(c_r.Qt_Segundos),2,'0'); 
   end if; 
 close c; 
 return v_hora; 
exception 
 when others then 
  return 'erro'; 
End; 
/

[Victor_Ni]

Good afternoon, Spernega.

Your function was good. simpler than the solution I developed.

I will do more test in this function.

Thanks.

[spernega]

Good evening Victor,

Good luck, adapt the return the way you need it.

in hours or minutes, just multiply the variables.