Intervals go ISO-8601 #JoelKallmanDay

Another year, and it's time to write an entry for the #JoelKallmanDay.

My recent discovery in Oracle Database 21c, the innovation release, is how intervals can also be in ISO-8601 format.
The ISO-8601 format is an international standard for describing Date and Time formats in data exchanges.
There are two flavors of interval in the Oracle Database, "Day to Second" and "Year to Month" and there are specialized functions to convert strings in a certain format to an interval. The following example creates a "Day to Second" interval of 4 days, 3 hours, 25 minutes and 42 seconds:

SQL> select to_dsinterval ('4 3:25:42')
  2    from dual
  3* /

TO_DSINTERVAL('43:2
-------------------
+04 03:25:42.000000

Feeding this "Day to Second" interval into a JSON_SCALAR function will return the ISO-8601 standard for this interval:

SQL> select json_scalar (to_dsinterval ('4 3:25:42'))
  2    from dual
  3* /

JSON_SCALAR(TO_DSINTERVAL('43:25:42'))                                   
-------------------------------------------------------------------------
"P4DT3H25M42S" 

The reverse is also true, it is possible to feed an ISO-8601 interval into the TO_DSINTERVAL function and get an Oracle interval returned:

SQL> select to_dsinterval ('P7DT4H12M53S')
  2    from dual
  3* /

TO_DSINTERVAL('P7DT
-------------------
+07 04:12:53.000000

This works the same for "Year to Day" intervals.