以 HH:MM 格式添加时间 -SQL 查询 [英] Adding Time in HH:MM Format -SQL Query

问题描述

先生/妈妈，

我使用的是 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;

这篇关于以 HH:MM 格式添加时间 -SQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！