This discussion is archived
11 Replies Latest reply: Feb 22, 2013 5:49 AM by John Spencer RSS

Sql ---sum of Date-time format ......

Xandot Newbie
Currently Being Moderated
id time
1 1.20
2 2.30
3 4.20
4 5.30
5 1.00

result expected = 14:40 or (if AM/PM so result is ???)
how can i calculate the sum of time ???
please give me some fast tricks of handling date/time format in oracle....
  • 1. Re: Sql ---sum of Date-time format ......
    rp0428 Guru
    Currently Being Moderated
    Welcome to the forum!

    Whenever you post provide your 4 digit Oracle version (SELECT * FROM V$VERSION)
    >
    id time
    1 1.20
    2 2.30
    3 4.20
    4 5.30
    5 1.00

    result expected = 14:40 or (if AM/PM so result is ???)
    how can i calculate the sum of time ???
    please give me some fast tricks of handling date/time format in oracle....
    >
    Oracle doesn't have 'TIME' datatypes. It has DATE, TIMESTAMP and other similar but they all include DATE.

    So what is the source of the data? What datatype and format is the data stored in? (please don't say VARCHAR2 or NUMBER).
  • 2. Re: Sql ---sum of Date-time format ......
    HamidHelal Guru
    Currently Being Moderated
    942425 wrote:
    id time
    1 1.20
    2 2.30
    3 4.20
    4 5.30
    5 1.00

    result expected = 14:40 or (if AM/PM so result is ???)
    how can i calculate the sum of time ???
    please give me some fast tricks of handling date/time format in oracle....
    Always post the database version and all related script. for example create table insert data statement.

    what is the data type of the filed ?
    if number, check the following code
    SELECT SUM(NVL(ROUND(TRUNC(DAC_OT_HOUR)+((TO_NUMBER (SUBSTR (DAC_OT_HOUR, INSTR (DAC_OT_HOUR,'.',1,1)+1)))/60)),0)) 
    FROM DA_CHECK
    Hope this helps


    Edited by: HamidHelal on Feb 21, 2013 10:34 AM

    Edited by: BluShadow on 21-Feb-2013 14:27
    removed begging for points. See {message:id=9698382}
  • 3. Re: Sql ---sum of Date-time format ......
    sb92075 Guru
    Currently Being Moderated
    How do I ask a question on the forums?
    SQL and PL/SQL FAQ
  • 4. Re: Sql ---sum of Date-time format ......
    Xandot Newbie
    Currently Being Moderated
    i want to store time in date format column.so how can i calculate
    the total of time in HH:MI format like the above example (please replace '.' to ':')
    oracle version -10.2.0.4.0
  • 5. Re: Sql ---sum of Date-time format ......
    Xandot Newbie
    Currently Being Moderated
    This query in not given me correct output ..

    i going to store time in data format.my question is simple that how can we calculate time .
    and output only in HH:MM format...
  • 6. Re: Sql ---sum of Date-time format ......
    bencol Pro
    Currently Being Moderated
    this works for total under 24 hours
      1  with t(id, tm)
      2   as (     select 1,to_date('01.20','hh.mi') from dual
      3  union all select 2,to_date('02.30','hh.mi') from dual
      4  union all select 3,to_date('04.20','hh.mi') from dual
      5  union all select 4,to_date('05.30','hh.mi') from dual
      6  union all select 5,to_date('01.00','hh.mi') from dual
      7       )
      8  select TO_CHAR(trunc(sysdate,'dd') + sum(TO_DATE(TO_CHAR(tm,'hh24:mi'),'hh24:mi')-trunc(sysdate,'mm')),'hh24:mi')
      9* from   t
    /
    
    TO_CH
    _____
    14:40
    Ben
  • 7. Re: Sql ---sum of Date-time format ......
    Solomon Yakobson Guru
    Currently Being Moderated
    942425 wrote:
    i want to store time in date format column.
    It doesn't look like DATE is proper datatype to use. I'd use INTERVAL:
    with t as (
               select 1 id,1.20 time_col from dual union all
               select 2,2.30 from dual union all
               select 3,4.20 from dual union all
               select 4,5.30 from dual union all
               select 5,1.00 from dual
              )
    select  numtodsinterval(sum(trunc(time_col) * 60 + 100 * mod(time_col,1)),'minute') total_time
      from  t
    /
    
    TOTAL_TIME
    -----------------------------
    +000000000 14:40:00.000000000
    
    SQL> 
    SY.
  • 8. Re: Sql ---sum of Date-time format ......
    EdStevens Guru
    Currently Being Moderated
    942425 wrote:
    i want to store time in date format column.so how can i calculate
    the total of time in HH:MI format like the above example (please replace '.' to ':')
    oracle version -10.2.0.4.0
    The total of time is not the same as a point in time. DATE datatype stores a point in time.

    What is the result of 21-Feb + 3-March?
    What is the result of 10:00 am + 6:00 pm?
  • 9. Re: Sql ---sum of Date-time format ......
    Xandot Newbie
    Currently Being Moderated
    ans1:-10 days (not including time)
    ans2:-8:00(total hrs)
  • 10. Re: Sql ---sum of Date-time format ......
    EdStevens Guru
    Currently Being Moderated
    942425 wrote:
    ans1:-10 days (not including time)
    ans2:-8:00(total hrs)
    And you just made my point. 21 Feb + 3 March, with some weird interpretation of date arithmetic, could be construed to be 10 days, but 10 days is not a date and cannot be stored in a DATE datatype.

    And at the same time you missed my point. You really can't add a date to a date. You add an interval to a date. And if you subtract a date from a date, you get an interval. And an interval is not a date, and cannot be stored as a date.

    Try again .. Compute 22 Feb + 3 Mar and tell me what value you want to put in your DATE column.

    If you compute the difference in two given times to be say, 47 hours and 23 minutes, we commonly represent that as "47:23", which is the same format as we use to represent a time, but an interval of time is not the same as "a point in time". And a DATE column stores a point in time. If you want to store an interval of time, you need to use a NUMBER and establish what that number represents ... years, or days, or minutes, or nanoseconds .....
  • 11. Re: Sql ---sum of Date-time format ......
    John Spencer Oracle ACE
    Currently Being Moderated
    942425 wrote:
    ans1:-10 days (not including time)
    ans2:-8:00(total hrs)
    Now, I would say that 22-Feb + 03-Mar is 25-Apr, at least in a non-leap year.

    Feb 22 is day 53 of the year and Mar 3 is day 62 and Apr 25 is day 115.

    I'm with Ed, you can't add dates.

    John

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points