CODEWITHSUNDEEP
oracle-databaseiso8601
cagcowboy
cagcowboy

Reputation: 30888

How can I easily parse an ISO 8601 duration/period in Oracle?

Is there an easy/inbuilt way to convert ISO 8601 durations/periods within Oracle?

So let's say I want to turn PT0H4M51S into the number of seconds - is there a way to do this without writing my own parser/code?

Thanks!

Upvotes: 1

Views: 1244

Answers (1)

Alex Poole
Alex Poole

Reputation: 191570

As Leo mentioned in comments, you can use the to_dsinterval() function, which accepts ISO formats (up to days; months and years can be handled by to_yminterval() instead); but that gives you an INTERVAL DAY TO SECOND data type:

select to_dsinterval('PT0H4M51S')
from dual;

DURATION  
-----------
0 0:4:51.0

You could convert that to interval seconds with the extract function:

with t as (
  select to_dsinterval('PT0H4M51S') as duration
  from dual
)
select (extract (day from duration) * 86400)
  + (extract (hour from duration) * 3600)
  + (extract (minute from duration) * 60)
  + extract (second from duration) as seconds
from t;

   SECONDS
----------
       291

If you only have a time duration - so it can't be longer than a day - then you could use date manipulation instead:

select (to_date('PT0H4M51S', '"PT"HH24"H"MI"M"SS"S"')
 - trunc(sysdate, 'MM')) * 86400 as seconds
from dual;

   SECONDS
----------
       291

If you convert a string containing just a time to a DATE type in Oracle, it defaults to the first day of the current month. And trunc(sysdate, 'MM') gives you midnight the first day of the current month. The difference is the duration in fractions of a day, so multiply by 60*60*24 to get the difference in seconds.

But you have to supply the fixed text parts (P, T, H, M and S) as quoted literals, so you'd have to know exactly the format you're going to receive. The to_dsinterval() version will work with any valid format.

Upvotes: 2

Related Questions