IntervalStyle

Definition PG 18 manual

Sets the display format for interval values. The value sql_standard will produce output matching SQL standard interval literals. The value postgres (which is the default) will produce output matching PostgreSQL releases prior to 8.4 when the DateStyle parameter was set to ISO. The value postgres_verbose will produce output matching PostgreSQL releases prior to 8.4 when the DateStyle parameter was set to non-ISO output. The value iso_8601 will produce output matching the time interval “format with designators” defined in section 4.4.3.2 of ISO 8601.

The IntervalStyle parameter also affects the interpretation of ambiguous interval input. See Section 8.5.4 for more information.

How it works

IntervalStyle sets the display format for interval values. Its four modes target PostgreSQL compatibility, verbose output, SQL-standard literals, or ISO 8601; it can also change how ambiguous interval input is read.

IntervalStyle is a USER-context setting. An authorized role can change it for a session, while ALTER ROLE or ALTER DATABASE can establish a default for future sessions.

It changes representation, parsing, or locale behavior at the client boundary rather than physical storage. Coordinate it with the other locale and formatting settings and with driver-native binary or typed protocols.

Operational considerations

Changing IntervalStyle in one session and assuming role defaults, database defaults, or other pooled sessions changed with it.

Assuming a presentation setting changes stored values or server-side type semantics.

Changing a role or database default without testing text-parsing clients, exports, and pooled sessions.

Changing IntervalStyle globally without a rollback plan and a client or operational compatibility test.

Workload guidance

OLAP: Pin IntervalStyle for export, reporting, and reproducible analytical jobs; prefer explicit SQL formatting when a file or API has a durable schema.

OLTP: Treat IntervalStyle as an application contract, not a performance knob. Standardize it per role or connection pool and keep wire formats explicit where clients parse text.

SMALL: Keep the upstream or locale-derived value unless a client requires another one. A smaller server gains no capacity from changing IntervalStyle.