IntervalStyle is DateStyle’s younger sibling, and it inherited the family trait: it looks like an output-formatting preference, but it also changes how PostgreSQL parses your input. We covered the date version of this trick in DateStyle. The interval version is less famous and, in one specific case, nastier.
The default is postgres, the context is user, and the parameter arrived in PostgreSQL 8.4 along with an overhaul of interval input and output. There are four styles. Here is interval '1 year 2 months 3 days 4:05:06' in each of them:
| Style | Output |
|---|---|
postgres |
1 year 2 mons 3 days 04:05:06 |
postgres_verbose |
@ 1 year 2 mons 3 days 4 hours 5 mins 6 secs |
sql_standard |
+1-2 +3 +4:05:06 |
iso_8601 |
P1Y2M3DT4H5M6S |
postgres matches what PostgreSQL printed before 8.4 when DateStyle was ISO; postgres_verbose matches what it printed when DateStyle was anything else, complete with the @ and an optional trailing ago for negation. Nobody chooses postgres_verbose on purpose. It survives so that output older than the parameter itself still round-trips.
The interesting two are sql_standard and iso_8601, and each has a trap.
The SQL standard requires every field of an interval to have the same sign, so a leading minus applies to the whole value. PostgreSQL traditionally treats each field as independently signed. IntervalStyle decides which rule applies when you hand it an ambiguous literal:
1 SET IntervalStyle = 'postgres'; 2 SELECT extract(epoch from interval '-1 2:03:04'); 3 -- -79016 (minus one day, PLUS 2:03:04) 4 5 SET IntervalStyle = 'sql_standard'; 6 SELECT extract(epoch from interval '-1 2:03:04'); 7 -- -93784 (minus one day, minus 2:03:04)
Same literal, two values, a bit over four hours apart. sql_standard is the only one of the four values that changes the meaning of data on the way in, and it does so for exactly the kind of literal people write casually. The defense, which the documentation recommends and I second: put an explicit sign on every field of any negative interval. '-1 days -2:03:04' means the same thing under every setting.
The iso_8601 trap is at the other end of the pipe. For mixed-sign intervals, PostgreSQL emits negative designators: P-1Y-2M3DT-4H-5M-6S. Base ISO 8601 has no negative durations (signed durations arrived later, as an extension in ISO 8601-2:2019), so a strict parser will reject output PostgreSQL produced in the name of ISO 8601. If your intervals can go negative, switching to iso_8601 does not buy interoperability by itself.
And there is a quieter problem that applies to changing the style at all. When results come back in text format, which is how most drivers operate most of the time, an interval column arrives as exactly the strings in the table above, and the driver parses them with code written for one style; almost always the default. node-postgres, to pick one, uses an interval parser that only understands postgres style, and its documentation tells you to put IntervalStyle back if you’ve changed it. Set iso_8601 per-database or per-role to make one API endpoint prettier, and every other client reading intervals starts mis-parsing or throwing errors in code nobody touched. (Binary-format results are immune; the style only exists in the text representation.)
So: leave the server at postgres, and treat this as a session-scoped parameter. If you need ISO 8601 durations for an export or an API, SET LOCAL IntervalStyle = 'iso_8601' in the transaction that produces them, or format in the application. Never set sql_standard anywhere broader than a session, because it changes what your input means, not just what your output looks like. And sign every field of your negative intervals; ambiguity you never send is ambiguity no setting can reinterpret.