oracle查询时间带有秒,sql - 获取oracle sql中的时间戳总和（小数秒） - SO中文参考 - www.soinside.com...

使用INTERVAL DAY TO SECOND类型存储数据，然后使用自定义聚合函数对它们求和。 (但是，如果你必须使用TIMESTAMP数据类型，那么你可以通过减去一个时期INTERVAL - 即TIMESTAMP将其转换为TIMESTAMP '0001-01-01 00:00:00.000000)。

Oracle 11g R2架构设置：

CREATE TABLE LapTimes( time TIMESTAMP(6) )

/

INSERT INTO LapTimes ( time )

SELECT TIMESTAMP '0001-01-01 00:01:23.652148' FROM DUAL UNION ALL

SELECT TIMESTAMP '0001-01-01 00:01:24.123456' FROM DUAL UNION ALL

SELECT TIMESTAMP '0001-01-01 00:01:22.987654' FROM DUAL

/

自定义聚合功能：

CREATE OR REPLACE TYPE IntervalAggregation AS OBJECT(

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

STATIC FUNCTION ODCIAggregateInitialize(

ctx IN OUT IntervalAggregation

) RETURN NUMBER,

MEMBER FUNCTION ODCIAggregateIterate(

self IN OUT IntervalAggregation,

value IN INTERVAL DAY TO SECOND

) RETURN NUMBER,

MEMBER FUNCTION ODCIAggregateTerminate(

self IN OUT IntervalAggregation,

returnValue OUT INTERVAL DAY TO SECOND,

flags IN NUMBER

) RETURN NUMBER,

MEMBER FUNCTION ODCIAggregateMerge(

self IN OUT IntervalAggregation,

ctx IN OUT IntervalAggregation

) RETURN NUMBER

);

/

CREATE OR REPLACE TYPE BODY IntervalAggregation

IS

STATIC FUNCTION ODCIAggregateInitialize(

ctx IN OUT IntervalAggregation

) RETURN NUMBER

IS

BEGIN

ctx := IntervalAggregation( NULL );

RETURN ODCIConst.SUCCESS;

END;

MEMBER FUNCTION ODCIAggregateIterate(

self IN OUT IntervalAggregation,

value IN INTERVAL DAY TO SECOND

) RETURN NUMBER

IS

BEGIN

IF value IS NULL THEN

NULL;

ELSIF self.value IS NULL THEN

self.value := value;

ELSE

self.value := self.value + value;

END IF;

RETURN ODCIConst.SUCCESS;

END;

MEMBER FUNCTION ODCIAggregateTerminate(

self IN OUT IntervalAggregation,

returnValue OUT INTERVAL DAY TO SECOND,

flags IN NUMBER

) RETURN NUMBER

IS

BEGIN

returnValue := self.value;

RETURN ODCIConst.SUCCESS;

END;

MEMBER FUNCTION ODCIAggregateMerge(

self IN OUT IntervalAggregation,

ctx IN OUT IntervalAggregation

) RETURN NUMBER

IS

BEGIN

IF self.value IS NULL THEN

self.value := ctx.value;

ELSIF ctx.value IS NULL THEN

NULL;

ELSE

self.value := self.value + ctx.value;

END IF;

RETURN ODCIConst.SUCCESS;

END;

END;

/

CREATE FUNCTION IntervalSum( value INTERVAL DAY TO SECOND )

RETURN INTERVAL DAY TO SECOND

PARALLEL_ENABLE AGGREGATE USING IntervalAggregation;

/

查询1：

SELECT IntervalSum( time - TIMESTAMP '0001-01-01 00:00:00.000000' ) AS totalLapTime

FROM LapTimes

| TOTALLAPTIME |

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

| 0 0:4:10.763258 |

查询2：

-- INSERT INTO YOUR_OTHER_TABLE ( TotalLapTime )

SELECT TIMESTAMP '0001-01-01 00:00:00.000000'

+ NUMTODSINTERVAL(

SUM(

EXTRACT( MINUTE FROM time ) * 60

+ EXTRACT( SECOND FROM time )

),

'SECOND'

) AS TotalLapTime

FROM LapTimes

| TOTALLAPTIME |

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

| 1-01-01 00:04:10.763258 |