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

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?

CodePudding user response：

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
)

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
)

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.