如何在 Oracle 中提取小时和分钟（从日期）作为数字？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/49587828/
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 extract Hours and Minutes (from Date) as a number in Oracle?
提问by dwalker
What I did:
我做了什么：
SELECT TO_CHAR(SYSDATE, 'HH24:MI') FROM dual;
What i got:
我得到了什么：
13:30
What I want :
我想要的是 ：
13.50
I also tried the EXTRACT function then summed hours and minutes but still not working.
我还尝试了 EXTRACT 函数，然后将小时和分钟相加，但仍然无法正常工作。
回答by Gordon Linoff
If you want the actual time (not just hours and minutes), you can do:
如果您想要实际时间（不仅仅是小时和分钟），您可以执行以下操作：
select ( sysdate - trunc(sysdate) ) * 24, sysdate
from dual;
If you just want just hours and minutes:
如果您只需要几小时和几分钟：
select extract(hour from current_timestamp) + extract(minute from current_timestamp) / 60
from dual
回答by mathguy
If you want the time-of-day (hours, minutes and seconds) expressed in fractional hours, then you can use
如果您想要以小数小时表示的时间（小时、分钟和秒），那么您可以使用
( sysdate - trunc(sysdate) ) * 24
as Gordon has shown already.
正如戈登已经表明的那样。
If you want to truncate the seconds and just convert the hour and minute into hours, then you can use
如果您想截断秒数并将小时和分钟转换为小时，那么您可以使用
( trunc(sysdate, 'mm') - trunc(sysdate) ) * 24
回答by APC
We can use the extract()functionality to get the hour and minutes. Note that this requires converting dates to a timestamp datatype (or using systimestamp in your example).
我们可以使用该extract()功能来获取小时和分钟。请注意，这需要将日期转换为时间戳数据类型（或在您的示例中使用 systimestamp）。
Once you have the hours and minutes you can do arithmetic on them and format them to fit your needs. For instance:
一旦您有了小时和分钟，您就可以对它们进行算术运算并格式化它们以满足您的需要。例如：
with cte as (
select to_timestamp('2018-03-31 13:30:00', 'yyyy-mm-dd hh24:mi:ss') as dt
from dual
) , tt as (
select extract(hour from dt) as hr
, round(extract(minute from dt)/60,2) as mn
from cte
)
select to_char(hr,'09')||trim(to_char(mn,'.00')) as your_time
from tt
/
... will display 13.50.
... 将显示13.50.
Here's a SQL Fiddle demo for you.
