How to present an ISO 8601 time string more readably

Question

I have an Oracle 12c database column called "lag_time" which is VARCHAR2, with values in the following format:

lag_time
PT2H16M33S
PT11H40M12S
PT11M33S
P17DT12H32M47S
P233DT11H21M54S
PT10M28S
....
....

I need some suggestions and solutions on how to present this in a more readable way - preferably as total minutes. How can I do that?

Answer 1

You can use the to_dsinterval() function to convert your strings to intervals:

to_dsinterval(lag_time)

And you can then extract the various elements, multiply each of them to get the equivalent minute value, and add them together; for complete minutes:

select lag_time, lag_interval,
  (extract(day from lag_interval) * 1440)
  + (extract(hour from lag_interval) * 24)
  + extract(minute from lag_interval) as total_minutes
from (
  select lag_time, to_dsinterval(lag_time) as lag_interval
  from your_table
)

LAG_TIME	LAG_INTERVAL	TOTAL_MINUTES
PT2H16M33S	+000000000 02:16:33.000000000	64
PT11H40M12S	+000000000 11:40:12.000000000	304
PT11M33S	+000000000 00:11:33.000000000	11
P17DT12H32M47S	+000000017 12:32:47.000000000	24800
P233DT11H21M54S	+000000233 11:21:54.000000000	335805
PT10M28S	+000000000 00:10:28.000000000	10

or to include fractional minutes:

select lag_time, lag_interval,
  (extract(day from lag_interval) * 1440)
  + (extract(hour from lag_interval) * 24)
  + extract(minute from lag_interval)
  + (extract(second from lag_interval) / 60) as total_minutes
from (
  select lag_time, to_dsinterval(lag_time) as lag_interval
  from your_table
)

LAG_TIME	LAG_INTERVAL	TOTAL_MINUTES
PT2H16M33S	+000000000 02:16:33.000000000	64.55
PT11H40M12S	+000000000 11:40:12.000000000	304.2
PT11M33S	+000000000 00:11:33.000000000	11.55
P17DT12H32M47S	+000000017 12:32:47.000000000	24800.78333333333333
P233DT11H21M54S	+000000233 11:21:54.000000000	335805.9
PT10M28S	+000000000 00:10:28.000000000	10.466666666666666667

db<>fiddle

Of course, since you're storing strings you won't necessarily have valid values; you can handle that with the default ... on conversion error clause if you need to, but it would be better to store an actual interval value. If you really need the string value you could also add a virtual column to your table that converts it to an interval.

The column name suggests to me that you will only ever have positive values; you'd need to do a bit more work to handle negative values properly.