Home » SQL & PL/SQL » SQL & PL/SQL » adding two numbers to get hours:minutes format (oracle 9i)
adding two numbers to get hours:minutes format [message #464817] Fri, 09 July 2010 11:40 Go to next message
mohaninforakle
Messages: 55
Registered: January 2009
Member


Dear friend,

I have a query to add two numbers and get results in hours:minutes format.Example I want to add 12.20 and 6.15 and get result in hours and minutes like 18.35 (hours & minutes).if minutes that is after precision exceed more than 60 it should treat as 1 hour.like i want to add

12.35 (number 1) before precision its hour and after its minutes
06.25 (number 2)
04.25 (number 3)
-----
23.25 (23 hours and 25 minutes)
-----

Please help me on this to solve my problem.Am waiting for reply from you all.Thanks in advance.
Re: adding two numbers to get hours:minutes format [message #464818 is a reply to message #464817] Fri, 09 July 2010 11:44 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: adding two numbers to get hours:minutes format [message #464819 is a reply to message #464818] Fri, 09 July 2010 11:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68773
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data.

Before, Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

SQL> with 
  2    data as (
  3      select 12.20 val from dual
  4      union all
  5      select 6.15 from dual
  6    ),
  7    total as (
  8      select sum(60*trunc(val)+100*mod(val,1)) total
  9      from data
 10    )
 11  select trunc(total/60)+mod(total,60)/100 result
 12  from total
 13  /
    RESULT
----------
     18.35


Regards
Michel
Re: adding two numbers to get hours:minutes format [message #464821 is a reply to message #464819] Fri, 09 July 2010 12:13 Go to previous messageGo to next message
mohaninforakle
Messages: 55
Registered: January 2009
Member

Sorry i will correct how to post from my next post.
Thanks for your reply really its good.Thanks a lot.
Re: adding two numbers to get hours:minutes format [message #464839 is a reply to message #464821] Fri, 09 July 2010 17:31 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
SQL> -- <= 24hrs
SQL> with
  2    data as (
  3      select 12.20 val from dual
  4      union all
  5      select 6.15 from dual
  6    ),
  7    total as (
  8      select sum(60*trunc(val)+100*mod(val,1)) total
  9      from data
 10    )
 11  select trunc(total/60)+mod(total,60)/100 result
 12  from total;

    RESULT
----------
     18.35

SQL> -- more than 24hrs OK?
SQL> with
  2    data as (
  3      select 22.20 val from dual
  4      union all
  5      select 6.15 from dual
  6    ),
  7    total as (
  8      select sum(60*trunc(val)+100*mod(val,1)) total
  9      from data
 10    )
 11  select trunc(total/60)+mod(total,60)/100 result
 12  from total;

    RESULT
----------
     28.35

SQL> -- using day fractions and dates handles >= 24hrs
SQL> with X as (select ((to_date('22.20', 'hh24.mi')-to_date('00:00','hh24.mi'))+
  2                     (to_date( '6.15', 'hh24.mi')-to_date('00:00','hh24.mi'))
  3                    ) dy_fract from dual)
  4  select TO_CHAR (TRUNC (dy_fract)) || ' day ' ||
  5           TO_CHAR (TRUNC (SYSDATE) + MOD (dy_fract, 1), 'HH24:MI') from X;

TO_CHAR(TRUNC(DY_FRACT))||'DAY'||TO_CHAR(TRUNC(SYS
--------------------------------------------------
1 day 04:35

SQL> -- or using Michel's more fancy format...
SQL> with X as (
  2      select '22.20' val from dual
  3      union all
  4      select '6.15' from dual
  5            ),
  6       Y as (select sum((to_date(val, 'hh24.mi')-to_date('00:00','hh24.mi'))) tot
  7             from X
  8            )
  9   select TO_CHAR (TRUNC (tot)) || ' day ' ||
 10          TO_CHAR (TRUNC (SYSDATE) + MOD (tot, 1), 'HH24:MI') from Y;

TO_CHAR(TRUNC(TOT))||'DAY'||TO_CHAR(TRUNC(SYSDATE)
--------------------------------------------------
1 day 04:35

SQL>
Re: adding two numbers to get hours:minutes format [message #464840 is a reply to message #464821] Fri, 09 July 2010 17:53 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
curious problem & curious answer

  1  with
  2	   data as (
  3	     select 36.75 val from dual
  4	     union all
  5	     select 6.35 from dual
  6	   ),
  7	   total as (
  8	     select sum(60*trunc(val)+100*mod(val,1)) total
  9	     from data
 10   )
 11   select trunc(total/60)+mod(total,60)/100 result
 12*  from total
SQL> /

    RESULT
----------
      43.5
Re: adding two numbers to get hours:minutes format [message #464862 is a reply to message #464839] Sat, 10 July 2010 00:44 Go to previous message
Michel Cadot
Messages: 68773
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
-- more than 24hrs OK?

Yes as OP said he wants to "get result in hours and minutes" not days/hours/minutes.

Regards
Michel
Previous Topic: Determine rows based on their 'grouping'
Next Topic: SP executes fine in one server but no in another one
Goto Forum:
  


Current Time: Thu Dec 25 09:02:44 CST 2025