Menu
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?
753
Briefly, the ISO 8601 notation consists of a P character, followed by years, months, weeks, and days, followed by a T character, followed by hours, minutes, and seconds with a decimal part, each with a single-letter suffix that indicates the unit. Any zero components may be omitted.
PostgreSQL uses 4 bytes to store a date value. The range of values for date values in PostgreSQL is 4713 BC to 5874897 AD. When storing a date value, PostgreSQL uses the yyyy-mm-dd format e.g. 1994-10-27. This format is also used in PostgreSQL for inserting data.
PostgreSQL supports the full set of SQL date and time types, shown in Table 8.9. The operations available on these data types are described in Section 9.9. Dates are counted according to the Gregorian calendar, even in years before that calendar was introduced (see Section B.6 for more information).
Postgres uses the DATE data type for storing different dates in YYYY-MM-DD format. It uses 4 bytes for storing a date value in a column. You can design a Postgres table with a DATE column and use the keyword DEFAULT CURRENT_DATE to use the current system date as the default value in this column.
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';
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With