oracle时间字段求和,如何对OracleSQL中存储hh24:mi:ss时间的列求和?-优快云博客

本文介绍了在Oracle数据库中如何对存储为hh24:mi:ss格式的时间字段进行求和，提供了两种方法：一种是通过转换为日期并计算差值求和；另一种是创建自定义对象和聚合函数实现INTERVAL DAY TO SECOND类型的求和。示例查询展示了具体的操作步骤。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

您可以将时间间隔计算为天数的分数,然后求和:

Oracle 11g R2架构设置

CREATE TABLE table_name ( PROCESS_TIME ) AS

SELECT '01:23:04' FROM DUAL UNION ALL

SELECT '23:00:00' FROM DUAL UNION ALL

SELECT '11:36:56' FROM DUAL;

查询1

SELECT SUM(

TO_DATE( PROCESS_TIME, 'HH24:MI:SS' )

- TO_DATE( '00:00:00', 'HH24:MI:SS' )

) AS num_days

FROM table_name

| NUM_DAYS |

|----------|

| 1.5 |

您还可以创建一个自定义对象来聚合

INTERVAL DAY TO SECOND

数据类型:

CREATE TYPE IntervalSumType AS OBJECT(

total INTERVAL DAY(9) TO SECOND(9),

STATIC FUNCTION ODCIAggregateInitialize(

ctx IN OUT IntervalSumType

) RETURN NUMBER,

MEMBER FUNCTION ODCIAggregateIterate(

self IN OUT IntervalSumType,

value IN INTERVAL DAY TO SECOND

) RETURN NUMBER,

MEMBER FUNCTION ODCIAggregateTerminate(

self IN OUT IntervalSumType,

returnValue OUT INTERVAL DAY TO SECOND,

flags IN NUMBER

) RETURN NUMBER,

MEMBER FUNCTION ODCIAggregateMerge(

self IN OUT IntervalSumType,

ctx IN OUT IntervalSumType

) RETURN NUMBER

);

CREATE OR REPLACE TYPE BODY IntervalSumType

STATIC FUNCTION ODCIAggregateInitialize(

ctx IN OUT IntervalSumType

) RETURN NUMBER

BEGIN

ctx := IntervalSumType( INTERVAL '0' DAY );

RETURN ODCIConst.SUCCESS;

END;

MEMBER FUNCTION ODCIAggregateIterate(

self IN OUT IntervalSumType,

value IN INTERVAL DAY TO SECOND

) RETURN NUMBER

BEGIN

IF value IS NOT NULL THEN

self.total := self.total + value;

END IF;

RETURN ODCIConst.SUCCESS;

END;

MEMBER FUNCTION ODCIAggregateTerminate(

self IN OUT IntervalSumType,

returnValue OUT INTERVAL DAY TO SECOND,

flags IN NUMBER

) RETURN NUMBER

BEGIN

returnValue := self.total;

RETURN ODCIConst.SUCCESS;

END;

MEMBER FUNCTION ODCIAggregateMerge(

self IN OUT IntervalSumType,

ctx IN OUT IntervalSumType

) RETURN NUMBER

BEGIN

self.total := self.total + ctx.total;

RETURN ODCIConst.SUCCESS;

END;

然后是一个自定义聚合函数:

CREATE FUNCTION SUM_INTERVALS( value INTERVAL DAY TO SECOND )

RETURN INTERVAL DAY TO SECOND

PARALLEL_ENABLE AGGREGATE USING IntervalSumType;

查询2

SELECT SUM_INTERVALS( TO_DSINTERVAL( '+0 '||PROCESS_TIME ) )

AS total_difference

FROM table_name

| TOTAL_DIFFERENCE |

|------------------|

| 1 12:0:0.0 |

更新-查询3

SELECT TO_CHAR( num_days * 24, 'FM99990' )

|| ':' || TO_CHAR( MOD( num_days * 24*60, 60 ), 'FM00' )

|| ':' || TO_CHAR( MOD( num_days * 24*60*60, 60 ), 'FM00' )

AS total_time

FROM (

SELECT SUM(

TO_DATE( PROCESS_TIME, 'HH24:MI:SS' )

- TO_DATE( '00:00:00', 'HH24:MI:SS' )

) AS num_days

FROM table_name

)

| TOTAL_TIME |

|------------|

| 36:00:00 |