CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
User014019
User014019

Reputation: 1247

How to get the hour and minutes in oracle sql?

I want to get and display the date, hours and minutes separately (3 columns: date, hours and minutes)

My script doesn't work. How to fix this? Thank you

Here's the sql:

 select trunc(t.create_time, 'DD') as createdate
            ,trunc(t.close_time, 'DD') as closedate
            ,datepart(hour, t.close_time()) as hours
            ,datepart(minute, t.close_time()) as minutes
            ,count(t.close_time) as total
      from app_account.otrs_ticket t
      left join app_account.otrs_user u
      on t.create_user_id=u.id
      where u.id not in (15,7,31,49,50,52,62,66,69,106,17,24,44,32,33,55,22,29,30,47,45,53,70,74,109,1,2,10,23,68,80,20,21,56,108,67)
      group by trunc(t.create_time, 'DD')
              ,trunc(t.close_time, 'DD')
              ,datepart(hour, t.close_time())
              ,datepart(minute, t.close_time())
      order by trunc(t.create_time, 'DD') desc

Upvotes: 3

Views: 38652

Answers (2)

Dave Lyndon
Dave Lyndon

Reputation: 796

I find the to_char() function to be the most flexible for converting and extracting date parts.

Here is an example that extracts the various elements from sysdate:

select to_char(sysdate, 'YYYY') as year, 
       to_char(sysdate, 'MM') as month, 
       to_char(sysdate, 'DD') as day,
       to_char(sysdate, 'HH24') as hour,
       to_char(sysdate, 'MI') as minute
from dual

You can supply different format parameters to get whatever result you require.

Upvotes: 11

Gordon Linoff
Gordon Linoff

Reputation: 1269503

The Oracle functions are extract() or to_char():

 select trunc(t.create_time, 'DD') as createdate,
        trunc(t.close_time, 'DD') as closedate,
        extract(hour from t.close_time) as hours,
        extract(minute from t.close_time) as minutes,
        count(t.close_time) as total
 from app_account.otrs_ticket t left join
      app_account.otrs_user u
      on t.create_user_id=u.id
 where u.id not in (15,7,31,49,50,52,62,66,69,106,17,24,44,32,33,55,22,29,30,47,45,53,70,74,109,1,2,10,23,68,80,20,21,56,108,67)
 group by trunc(t.create_time, 'DD'), trunc(t.close_time, 'DD')
          extract(hour from t.close_time) as hours,
          extract(minute from t.close_time) as minutes
 order by trunc(t.create_time, 'DD') desc;

I'm not sure what the () are after close_time, but they don't seem necessary.

Upvotes: 0

Related Questions