Alvaro Raygada Alvaro Raygada - 1 month ago 6
SQL Question

How to do a NVL to NUMTODSINTERVAL

SELECT NUMTODSINTERVAL(SUM(EXTRACT(HOUR FROM time1)), 'HOUR') +
NUMTODSINTERVAL(SUM(EXTRACT(MINUTE FROM time1)), 'MINUTE') AS HOURS
FROM table1 WHERE id1 = '123' AND value1 = 123


I need to do a NVL to NUMTODSINTERVAL and transform it to '0 00:00:00', because I need sum up total hours. However, when a value is NULL, the results are all NULL. How should I go about doing this?

Answer

You can't do what you suggested:

NVL(time1, TO_DSINTERVAL('0 00:00:00'))

because they are different data types; time1 is a timestamp, while the fixed value is an interval. You could use a timestamp literal instead:

NVL(time1, TIMESTAMP '1972-01-01 00:00:00')

... but that is essentially what @sagi suggested - just with a timestamp literal instead of a converted string. The date part doesn't matter, that can be anything, as long as the time is midnight.

You could also use a slight variant; since you only need the number of hours/minutes to be zero:

SELECT NUMTODSINTERVAL(SUM(NVL(EXTRACT(HOUR FROM time1), 0)), 'HOUR') +
   NUMTODSINTERVAL(SUM(NVL(EXTRACT(MINUTE FROM time1), 0)), 'MINUTE') AS HOURS
FROM table1 WHERE id1 = '123' AND value1 = 123

Quick demo with a CTE to provide a not-null value:

WITH table1 (id1, value1, time1) AS (
   SELECT '123', 123, TIMESTAMP '2016-01-01 12:34:56.789'
   FROM dual
)
SELECT NUMTODSINTERVAL(SUM(NVL(EXTRACT(HOUR FROM time1), 0)), 'HOUR') +
   NUMTODSINTERVAL(SUM(NVL(EXTRACT(MINUTE FROM time1), 0)), 'MINUTE') AS HOURS
   FROM table1 WHERE id1 = '123' AND value1 = 123;

HOURS
-----------------------------
+000000000 12:34:00.000000000

And with a null value:

WITH table1 (id1, value1, time1) AS (
   SELECT '123', 123, CAST(null AS TIMESTAMP)
   FROM dual
)
SELECT NUMTODSINTERVAL(SUM(NVL(EXTRACT(HOUR FROM time1), 0)), 'HOUR') +
   NUMTODSINTERVAL(SUM(NVL(EXTRACT(MINUTE FROM time1), 0)), 'MINUTE') AS HOURS
   FROM table1 WHERE id1 = '123' AND value1 = 123;

HOURS
-----------------------------
+000000000 00:00:00.000000000
Comments