Storing ISO 8601 duration in PostgreSQL
Asked Answered
B

1

7

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?

Burnaby answered 7/11, 2019 at 21:24 Comment(0)
B
12

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';
Burnaby answered 7/11, 2019 at 21:24 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.