Calculation of hours between two dates/hours

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

Poston Fri, 08 Jan 2010 1:08 pm

It didn't work out that your schema ... = X in the end I ended up working a fuction for that ... short and functional ... take a look.

Code: Select all
CREATE OR REPLACE function SEMASA.f_tempo_media ( day_fraction NUMBER)
return char

Years        NUMBER;
months       NUMBER;
days         NUMBER;
hrs          NUMBER;
mints        NUMBER;
sec          NUMBER;


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'));

Location: MS

Mateus Leonardi

Poston Fri, 12 Mar 2010 3:20 pm

Dear, I was watching the selects previous and I could not find a result for my.

I need to find a vague schedule to schedule service.
My application there is a table where you have Scheduled timetable.
Start date/time (DHPREVISTA) TEMPPRIVISTO End date/time.

Ex: I have Scheduled times: 08:00-09:00 11:00-12:00 I need the select returns the schedule q has vague of the 10 11 all this taking into account schedule of 8 the 12 of the 13:30 the 18:00 only during weekday and boundary between schedules of 60 minutes.

Thank you for your collaboration.

Thank you.

Poston Tue, 21 Dec 2010 8:47 am

OK I got this code
Code: Select all
SELECT TO_CHAR ( (datahorainicio + INTERVAL || ''' ||tempo || ''' || MINUTE), 'HH24:mi:ss')
(SELECT datahorainicio,
        TO_CHAR ( ROUND (   (   (datahorafim - datahorainicio) * 1440)  )) AS tempo
FROM atendimento);
But now I need to do a search with schedules from 12 min. How do I. time oexiste not in the table.
Location: Florianopolis

Priscila Fernandes

Poston Tue, 21 Dec 2010 9:06 am

Explain better your situation.

You have a table with starting and ending time and want to see what happened to 12 min??
Location: Parobé - RS

Sérgio Luiz Bonemberger Junior
Programador Junior
Parobé RS


Poston Thu, 15 Sep 2016 11:10 pm

Greetings. há I see times that there are many technicians with the same question, " how to calculate the time between two dates? ", just to recap. It is not a question of calculating the time between a date and the current date (sysdate), because it would be simple using the Oracle timestamp function.
Without knowing more details, very s ask: To know the time between dates?
There are a number of reasons. even more for a DBA, monitoring processes would be the most appropriate response to this question.

Now in practice, would someone managed to accurately create a script that works 100%?

This is not a challenge, I don't want to be repetitive in solution options.


Poston Fri, 16 Sep 2016 7:42 am

Good morning Victor, Has a function that I created and that is working fine.
At least until now I didn't get any error.

Code: 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;
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;
when others then
  return 'erro';
Location: São Paulo - SP

Poston Sat, 17 Sep 2016 6:04 pm

Good afternoon, spernega.

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

I'll do more testing in this function.

Thank you.

Poston Sat, 17 Sep 2016 7:04 pm

Good night Victor, good luck, adapt the way you need.

In hours or minutes, just multiply the variables.
Location: São Paulo - SP

  • See also
    Last Post