Date Subtraction

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
Post Reply
renatalucg
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 6
Joined: Wed, 26 Oct 2011 12:21 pm

Hi guys, how are you?

I need a help ... I am developing a report that I need to show the period between two dates (can be in hours or minutes, both). I have two fields and I am making a subtraction between them, but the value is coming out in a way that I do not understand:

Select all

24.08.2011 01:34:35 - 23.08.2011 23:48:16 = ,0738310185185185185185185185185185185185

Can anyone help me?

Sorry if there is another topic, but I looked and did not find it ..
Jota
Rank: Analista Júnior
Rank: Analista Júnior
Posts: 77
Joined: Wed, 17 Jun 2009 3:18 pm
Location: Blumenau - SC
Jonatas Jaqmam Pereira
Analista Desenvolvedor de Sistemas

Hello,

Try to multiply the result by 24 to get the hours.

For minutes, multique by 1440.

Try to see if you achieve the expected result.
renatalucg
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 6
Joined: Wed, 26 Oct 2011 12:21 pm

Hello Jonatas

I did this and resulted 1,77194444444444444444444444444444. Does it mean that I have a period of 1.7 hours?
Jota
Rank: Analista Júnior
Rank: Analista Júnior
Posts: 77
Joined: Wed, 17 Jun 2009 3:18 pm
Location: Blumenau - SC
Jonatas Jaqmam Pereira
Analista Desenvolvedor de Sistemas

Yes, your hours interval is less than 2 hours, because it's from 23:48 until 01:34.

In minutes it would give something around 106 minutes.
renatalucg
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 6
Joined: Wed, 26 Oct 2011 12:21 pm

Bacana was that even returned !!

Thanks for help !! : D
Jota
Rank: Analista Júnior
Rank: Analista Júnior
Posts: 77
Joined: Wed, 17 Jun 2009 3:18 pm
Location: Blumenau - SC
Jonatas Jaqmam Pereira
Analista Desenvolvedor de Sistemas

Just to clarify: the result you had found in your subtraction ,, 07385185185185185185185185185, is the result in days, ie 0.07 days.

Multiplying for 24 (24 hours of the day) brought us the value in hours.

Multiplying for 1440 (1440 minutes of the day) brought us the value in minutes.

Multiplying for 86400 (86400 seconds of the day) brought us the value in seconds.
renatalucg
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 6
Joined: Wed, 26 Oct 2011 12:21 pm

I understood .. nice to happen again and I want a different result! :)
ishii
Rank: Analista Júnior
Rank: Analista Júnior
Posts: 82
Joined: Tue, 28 Dec 2010 7:41 pm
Location: São Paulo - SP

Hello,

Try with the extraction as well.

Select all

select extract(hour from (datahorafinal- datahorainicial) day to second) +  
round(extract(minute from  (datahorafinal- datahorainicial)day to second)/60,2)  
from table;

[] S ISHII
Post Reply
  • Information
  • Who is online

    Users browsing this forum: Bing [Bot] and 1 guest