A PL/SQL function that formats an Oracle INTERVAL DAY TO SECOND value to a conformant ISO 8601 duration string.

format_iso_dsinterval.plsql

CREATE OR REPLACE FUNCTION format_iso_dsinterval(interval_in INTERVAL DAY TO SECOND) RETURN VARCHAR2
IS 
  --max value ever would be 'P999999999DT59H59M59.999999999S', a 31-char string 
  l_iso VARCHAR2(31) := 'P';
  l_day NUMBER;
  l_hour NUMBER; 
  l_minute NUMBER;
  l_second NUMBER;
  
BEGIN
  l_day := EXTRACT(DAY FROM interval_in);
  IF l_day <> 0 THEN 
     l_iso := l_iso || l_day || 'D';
  END IF;
  
  l_hour := EXTRACT(HOUR FROM interval_in);
  IF l_hour <> 0 THEN 
     l_iso := l_iso || l_hour || 'H';
  END IF;
  
  l_minute := EXTRACT(MINUTE FROM interval_in);
  IF l_minute <> 0 THEN 
     l_iso := l_iso || l_minute || 'H';
  END IF;
  
  l_second := EXTRACT(SECOND FROM interval_in);
  IF l_second <> 0 THEN 
     l_iso := l_iso || l_second || 'H';
  END IF;
  
  --if an interval of 0, return a neat P0Y0M
  IF l_iso = 'P' THEN 
    l_iso := l_iso || 'T0S';
  END IF;
  
  RETURN l_iso;  
END;