以 HH:MM 格式添加时间 -SQL 查询
问题描述：
先生/妈妈，
我使用的是 Oracle 11g.我有以下名称为Timeduration"的表格.它包含以小时和分钟为单位指定的时间.我想以小时和分钟为单位获取总时间.
I am using Oracle 11g.I have the following table by the name "Timeduration". It contains time specified in hours and minutes.I want to get the total time in hour and minutes.
Timeduration
05:37
06:40
03:45
02:50
07:58
我希望总时间为 25:30.我该如何为此编写代码?请帮帮我."
I want the total time as 25:30. How can i write the code for this?? Please help me out."
答
with t as (
select '05:37' as timeduration from dual union all
select '06:40' as timeduration from dual union all
select '03:45' as timeduration from dual union all
select '02:50' as timeduration from dual union all
select '07:58' as timeduration from dual
),
d as (
select
--
-- 4 Converting the sum obtained in (3) into
-- an interval
numtodsinterval(
--
-- 3 Summing the day fractions obtained in (2)
sum(
--
-- 1 Convert the string 'HH:MM' to a real date.
--
-- For example 05:37 becomes January 1st 0001 5:37
to_date('00010101' || t.timeduration, 'yyyymmddhh24:mi')
--
-- 2 Subtract January 1st 0001 from that date
--
-- The result is a number of a day's length
-- [1: 24 hours, 0.3: 8 hours etc)
- date '0001-01-01'),
--
'DAY'
) interval
from
t
)
select
(
-- 5 How many days are in the interval. Each day
-- corresponds to 24 hours:
extract (day from d.interval) * 24 +
--
-- 6 The hours «below» 24 hours must be added
-- seperatly:
extract (hour from d.interval)
)
--
-- 7 Hours are now extracted. Append the delimiter
|| ':' ||
(
--
-- 8 Finally: get the minutes from the interval
extract (minute from d.interval)
)
result
from d;