SQL 如何在oracle sql中获取小时和分钟？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26644466/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get the hour and minutes in oracle sql?
提问by User014019
I want to get and display the date, hours and minutes separately (3 columns: date, hours and minutes)
我想分别获取和显示日期、小时和分钟（3 列：日期、小时和分钟）
My script doesn't work. How to fix this? Thank you
我的脚本不起作用。如何解决这个问题？谢谢
Here's the sql:
这是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
回答by Dave Lyndon
I find the to_char()function to be the most flexible for converting and extracting date parts.
我发现该to_char()函数对于转换和提取日期部分是最灵活的。
Here is an example that extracts the various elements from sysdate:
这是从 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.
您可以提供不同的格式参数以获得所需的任何结果。
回答by Gordon Linoff
The Oracle functions are extract()or to_char():
Oracle 函数是extract()或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.
我不知道什么()是后close_time，但他们似乎没有必要。
