☰

对日期类型的字符串进行求和 - oracle

tag-icon • tag-icon sql date
对日期类型的字符串进行求和 - oracle

我的 TIME_AVG 列在 Oracle 中是 STRING，我需要将 SUM (TIME_AVG) 与 DATE 相加，但是 DATE 列只接受到 23:59:59，如何通过 oracle 中的查询来执行此操作？

TIME_AVG
42:12:57
空
98:31:06
20:16:12
04:00:31
05:18:39
05:18:06
50:09:12
22:59:27

答案1

DATE您不能将小时值大于 24 小时的值写入数据类型。如果您希望存储可能超过一天的持续时间，则应使用INTERVAL数据类型。

由于无法INTERVAL在 Oracle 中对数据类型进行本机求和，因此我会将字符串分解为小时、分钟和秒，对每条记录的总秒数求和，然后将每条记录的该值求和，最后将其显示为INTERVAL：

SELECT
    NUMTODSINTERVAL(SUM((TO_NUMBER(SUBSTR(TIME_AVG,1,2))*3600)+(TO_NUMBER(SUBSTR(TIME_AVG,4,2))*60)+TO_NUMBER(SUBSTR(TIME_AVG,7,2))), 'second')
FROM table
WHERE TIME_AVG IS NOT NULL

对于您问题中给出的值，结果是 10 天、8 小时、46 分钟和 10 秒：

+000000010 08:46:10.000000000

如果您想要完全重现原始数据的 hh:mm:ss 格式，您可以执行以下操作：

SELECT    
    TO_CHAR(FLOOR(SUM((TO_NUMBER(SUBSTR(TIME_AVG,1,2))*3600)+(TO_NUMBER(SUBSTR(TIME_AVG,4,2))*60)+TO_NUMBER(SUBSTR(TIME_AVG,7,2)))/3600)) || ':' ||
    TO_CHAR(FLOOR(MOD(SUM((TO_NUMBER(SUBSTR(TIME_AVG,1,2))*3600)+(TO_NUMBER(SUBSTR(TIME_AVG,4,2))*60)+TO_NUMBER(SUBSTR(TIME_AVG,7,2))),3600)/60)) || ':' ||
    TO_CHAR(MOD(SUM((TO_NUMBER(SUBSTR(TIME_AVG,1,2))*3600)+(TO_NUMBER(SUBSTR(TIME_AVG,4,2))*60)+TO_NUMBER(SUBSTR(TIME_AVG,7,2))),60))
FROM table
WHERE TIME_AVG IS NOT NULL

输出结果如下：

248:46:10

如果你真的、真的、真的必须将结果存储在 DATE 字段中，我想你可以将其存储为相对于纪元 (1970-01-01) 的时间：

SELECT
    TO_CHAR(TO_DATE('1-1-1970 00:00:00','DD-MM-YYYY HH24:Mi:SS') + (SUM((TO_NUMBER(SUBSTR(TIME_AVG,1,2))*3600)+(TO_NUMBER(SUBSTR(TIME_AVG,4,2))*60)+TO_NUMBER(SUBSTR(TIME_AVG,7,2)))/86400), 'YYYY-MM-DD HH24:Mi:SS')
FROM table
WHERE TIME_AVG IS NOT NULL

输出结果如下：

1970-01-11 08:46:10

相关内容