Storing ISO 8601 duration in PostgreSQL

Question

Need to store duration in ISO 8601 format P[n]Y[n]M[n]DT[n]H[n]M[n]S in PostgreSQL, then retrieve it in a script in the same format. In what data type to store ISO 8601 duration in PostgreSQL?

Answer 1

Googling shows that some people store ISO 8601 duration as VARCHAR. PostgreSQL though among other date/time types has an interval data type which can store and return duration in iso_8601 format. For example,

CREATE TABLE xyz(
  id SERIAL PRIMARY KEY,
  duration INTERVAL
);

Duration in ISO 8601 format

# SELECT duration FROM xyz;
 duration
----------
 PT0S
 PT1M28S
(2 rows)

The output style of interval can be set by SET intervalstyle command

SET intervalstyle = 'iso_8601';