CODEWITHSUNDEEP
sqloracle-database
User014019
User014019

Reputation: 1247

How to get hours and minutes by subtracting two dates?

I want to get the hours and minutes between two dates. But it's not working and I getting this error

    ORA-01843: not a valid month
    01843. 00000 -  "not a valid month"

Here's the script

     select q.name as queue_name
        ,to_date(t.create_time, 'dd-mon-yyyy hh24:mi:ss')
        ,to_date(t.close_time, 'dd-mon-yyyy hh24:mi:ss')
        ,NUMTOYMINTERVAL(t.close_time - t.create_time, 'DAY')*24
        ,NUMTOYMINTERVAL(t.close_time - t.create_time, 'DAY')*24*60
  from app_account.otrs_ticket t
  left join app_account.otrs_user u
  on t.create_user_id=u.id
  left join app_account.otrs_queue q
  on q.id=t.queue_id
  group by to_date(t.create_time, 'dd-mon-yyyy hh24:mi:ss')
          ,to_date(t.close_time, 'dd-mon-yyyy hh24:mi:ss')
          ,NUMTOYMINTERVAL(t.close_time - t.create_time, 'DAY')*24
        ,NUMTOYMINTERVAL(t.close_time - t.create_time, 'DAY')*24*60
        ,q.name
  order by to_date(t.create_time, 'dd-mon-yyyy hh24:mi:ss') desc;

Upvotes: 0

Views: 287

Answers (4)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59602

Cast your DATE (or whatever data type it is) to a TIMESTAMP. The difference gives you an INTERVAL value where you can extract all information. Should be like this:

EXTRACT(DAY FROM CAST(t.close_time AS TIMESTAMP) - CAST(t.create_time AS TIMESTAMP))*24*60
+ EXTRACT(HOUR FROM CAST(t.close_time AS TIMESTAMP) - CAST(t.create_time AS TIMESTAMP))*60
+ EXTRACT(MINUTE FROM CAST(t.close_time AS TIMESTAMP) - CAST(t.create_time AS TIMESTAMP)) AS MINUTES

Upvotes: 0

DirkNM
DirkNM

Reputation: 2664

Another way would be to calculate the values directly without using any oracle function:

Here is an example:

WITH q AS
(SELECT TO_DATE(create_time, 'YYYY-MM-DD HH24:MI') AS d_start,
        TO_DATE(close_time, 'YYYY-MM-DD HH24:MI')        AS d_end
   FROM app_account.otrs_ticket
)
SELECT FLOOR((d_end - d_start) * 24)                                                   AS hours,
       FLOOR(((d_end     - d_start) * 24 * 60) - (floor((d_end - d_start) * 24) * 60)) AS minutes
  FROM q;

Hope it helps!

Upvotes: 0

Hardik Parmar
Hardik Parmar

Reputation: 1051

Try this you will get atleast idea how to calculate time difference between two dates.

   SELECT floor(((date1-date2)*24*60*60)/3600)
            || ' HOURS ' ||
             floor((((date1-date2)*24*60*60) -
           floor(((date1-date2)*24*60*60)/3600)*3600)/60)
            || ' MINUTES ' ||
            round((((date1-date2)*24*60*60) -
             floor(((date1-date2)*24*60*60)/3600)*3600 -
            (floor((((date1-date2)*24*60*60) -
           floor(((date1-date2)*24*60*60)/3600)*3600)/60)*60) ))
          || ' SECS ' time_difference
     FROM dates;

    TIME_DIFFERENCE
    --------------------------------------------------------------------------------
    24 HOURS 0 MINUTES 0 SECS
    1 HOURS 0 MINUTES 0 SECS
    0 HOURS 1 MINUTES 0 SECS

For Futher Reference Please Refer This Link : Click Here

Upvotes: 1

Jens
Jens

Reputation: 69495

use NUMTODSINTERVAL(close_date - create_date, 'DAY') or NUMTOYMINTERVAL(close_date - create_date, 'DAY') for calculating the difference in days.

For hours multiply the value with 24 and for minutes with 24*60.

select q.name as queue_name
            ,to_date(t.create_time, 'dd-mon-yyyy hh24:mi:ss') as create_date
            ,to_date(t.close_time, 'dd-mon-yyyy hh24:mi:ss') as close_date
            ,NUMTODSINTERVAL(close_date - create_date, 'DAY') or NUMTOYMINTERVAL(close_date - create_date, 'DAY')*24
            ,NUMTODSINTERVAL(close_date - create_date, 'DAY') or NUMTOYMINTERVAL(close_date - create_date, 'DAY') *24*60
      from app_account.otrs_ticket t
      left join app_account.otrs_user u
      on t.create_user_id=u.id
      left join app_account.otrs_queue q
      on q.id=t.queue_id
      where q.name not like 'Facilities Management::%'
      and q.name not like 'HR::%'
      and q.name not like 'Raw%'
      and q.name not like 'Procurement::%'
      and q.name not like 'Facilities Management%'
      and q.name not like 'Junk%'
      and q.name not like 'Facility Request Test%'
      and q.name not like 'Misc%'
      and q.name not like 'POS::POS issue - need paper%'
      group by to_date(t.create_time, 'dd-mon-yyyy hh24:mi:ss') as create_date
              ,to_date(t.close_time, 'dd-mon-yyyy hh24:mi:ss') as close_date
      order by to_date(t.create_time, 'dd-mon-yyyy hh24:mi:ss') desc;

Upvotes: 1

Related Questions