sql - 如何在oracle sql中获取小时和分钟？

Question

我想分别获取并显示日期、小时和分钟(3 列:日期、小时和分钟)

我的脚本不起作用。如何解决这个问题？谢谢

这是 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

Answer 1

我发现 to_char() 函数对于转换和提取日期部分最灵活。

这是一个从 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

您可以提供不同的格式参数来获得所需的任何结果。