Forum Stats

  • 3,874,089 Users
  • 2,266,676 Discussions
  • 7,911,721 Comments

Discussions

Problem with subtraction - hour - hour

2877737
2877737 Member Posts: 45
edited Mar 16, 2018 8:59AM in SQL & PL/SQL

Hi,

in my table I have two columns including hour of start tasks (start_zad) and the second, hour when the tasks where ended (stop_zad).

I try to get information how long taks last. The hour is in format 24HH:MI (eg 23:44) and type of columns is varchar2.

My query is as below and I get wrong result

image

image

Thanks for all help

Paul

 
Tagged:
ManikRobert Angel2877737

Best Answer

  • Anton Scheffer
    Anton Scheffer Member Posts: 1,950 Gold Trophy
    edited Mar 14, 2018 7:38AM Answer ✓

    You mean something like

    with my_table as
    ( select '00:00' start_zad, '06:28' stop_zad from dual
    union all select '03:01', '04:26' from dual
    union all select '00:00', '05:31' from dual
    )
    select start_zad, stop_zad, to_char( trunc(sysdate) + ( to_date( stop_zad, 'hh24:mi' ) - to_date( start_zad, 'hh24:mi' ) ), 'hh24:mi' )
    from my_table

«13

Answers

  • 2877737
    2877737 Member Posts: 45
    edited Mar 14, 2018 7:21AM
  • Cookiemonster76
    Cookiemonster76 Member Posts: 3,410 Gold Trophy
    edited Mar 14, 2018 7:26AM

    It's better to copy and paste text from sqlplus rather than an image. Easier for the rest of us to see and work with.

    The result looks correct to me. It might not be in the format you want, but you never said what that was.

    Your output is in hours, with everything after the comma being decimal, not minutes.

    Manik
  • Paulzip
    Paulzip Member Posts: 8,801 Blue Diamond
    edited Mar 14, 2018 7:28AM

    Result looks fine to me.  Your result is decimalised number of hours.

    For example 5:31 - 0:00 = 5 hours and 31 minutes = Just over 5 and half hours = 5.51666667

    What do you think is wrong with it?

  • 2877737
    2877737 Member Posts: 45
    edited Mar 14, 2018 7:32AM

    I need the result in format 24hh:MI.

    So, when a person will read the report must ne,w that task lasted 4:34 it means 4 hours and 34 minutes.

    Paul.

  • Anton Scheffer
    Anton Scheffer Member Posts: 1,950 Gold Trophy
    edited Mar 14, 2018 7:38AM Answer ✓

    You mean something like

    with my_table as
    ( select '00:00' start_zad, '06:28' stop_zad from dual
    union all select '03:01', '04:26' from dual
    union all select '00:00', '05:31' from dual
    )
    select start_zad, stop_zad, to_char( trunc(sysdate) + ( to_date( stop_zad, 'hh24:mi' ) - to_date( start_zad, 'hh24:mi' ) ), 'hh24:mi' )
    from my_table

  • Paulzip
    Paulzip Member Posts: 8,801 Blue Diamond
    edited Mar 14, 2018 7:51AM

    Several ways to do this...  Intervals might make things easier for you to understand.

    with test(start_zad, stop_zad) as (

      select sysdate, sysdate + 0.51 from dual -- just over half a day, I've assumed you've already converted to date using to_date(..., 'HH24:MI')

    )

    , spans as (

      select numtodsinterval(stop_zad - start_zad, 'DAY') as elapsed

      from test

    )

    select

      extract(day from elapsed) * 24 + extract(hour from elapsed) || ':' ||

      extract(minute from elapsed) duration

    from spans

    If stop_zad and start_zad are always on the same day, then you can remove the

    extract(day from elapsed) * 24 +

    bit.

    Robert Angel
  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown
    edited Mar 14, 2018 7:49AM
    2877737 wrote:Hi,in my table I have two columns including hour of start tasks (start_zad) and the second, hour when the tasks where ended (stop_zad).I try to get information how long taks last. The hour is in format 24HH:MI (eg 23:44) and type of columns is varchar2.My query is as below and I get wrong result 
    image

    image

    Thanks for all help

    Paul

    The fact that you keep the time in a varchar is itself a design flaw.  It should be in a DATE column, which includes time down to the second.

  • 2877737
    2877737 Member Posts: 45
    edited Mar 14, 2018 7:59AM

    Thanks All, It works.

    Paul.

  • 2877737
    2877737 Member Posts: 45
    edited Mar 14, 2018 8:06AM

    @EdStevens

    I feel it isn't proper. Could You suggest how it transform before insert to table ? to_date ?

    Paul.

  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown
    edited Mar 14, 2018 8:13AM
    2877737 wrote:EdStevensI feel it isn't proper. Could You suggest how it transform before insert to table ? to_date ?Paul.

    where does the data originate before it is inserted?  A keyboard can only create strings, so of course you'd use to_date to convert keyboard input to a DATE type.  That's what to_date does. That's it's entire purpose.

This discussion has been closed.