Parameter · Client Connection Defaults / Locale and Formatting
IntervalStyle
Sets the display format for interval values.
- Default:
postgres - Type: enum
- Values: postgres · postgres_verbose · sql_standard · iso_8601
- Applies: Session (any user)
In plain words
An interval is a length of time, like "1 year 2 months 3 days". IntervalStyle only changes how such a value is written out as text. The value itself is the same.
Applications that parse interval text (instead of using a driver type) can break if this changes.
For DBAs and operators
Four styles:
postgres(default):1 year 2 mons 3 days 04:05:06.7postgres_verbose:@ 1 year 2 mons 3 days 4 hours 5 mins 6.7 secs, matching the output of releases before 8.4 when DateStyle was set to a non-ISO style.sql_standard:+1-2 +3 +4:05:06.7. It produces true SQL-standard literals only when all fields share a sign; otherwise explicit signs are added.iso_8601: ISO 8601 "format with designators":P1Y2M3DT4H5M6.7S.
IntervalStyle also affects input of ambiguous strings: with sql_standard, a leading minus sign applies to all following fields (-1 2:03:04 is fully negative); other styles treat each field's sign separately. Negative intervals are a common source of confusion: interval '-1 day 02:00' prints as -1 days +02:00:00 in the default style and @ 1 day -2 hours ago in the verbose one.
Like DateStyle it is reported to clients on change. For machine parsing, iso_8601 is the least ambiguous choice; for human reports, prefer to_char(interval, …) or extract()/justify_interval().
How to change it
Any user can change it for their session with SET.