When working with the interval data type in PostgreSQL, you can change the way the interval output is formatted.

You have a choice of four formats that your intervals can be output in.

The output format of the interval type can be set to one of the following styles: 

• sql_standard
• postgres
• postgres_verbose 
• iso_8601

The default style is postgres.

To change your interval format, use the SET intervalstyle command.

Examples

The following examples demonstrate the output of make_interval() when using the different output styles.

sql_standard

This style conforms to the SQL standard’s specification for interval literal strings, if the interval value meets the standard’s restrictions (either year-month only or day-time only, with no mixing of positive and negative components).

Otherwise the output looks like a standard year-month literal string followed by a day-time literal string, with explicit signs added to disambiguate mixed-sign intervals.

Here’s an example using the year-month.

SET intervalstyle = 'sql_standard';
SELECT make_interval(years => 1, months => 2);

Result:

1-2

Below is another example. This time I specify years, months, weeks, days, hours, minutes, and seconds.

SET intervalstyle = 'sql_standard';
SELECT make_interval(1, 2, 3, 4, 5, 6, 7);

Result:

+1-2 +25 +5:06:07

postgres

postgress is the default setting.

This style matches the output of Postgres releases prior to 8.4 when the DateStyle parameter was set to ISO.

SET intervalstyle = 'postgres';
SELECT make_interval(1, 2, 3, 4, 5, 6, 7);

Result:

1 year 2 mons 25 days 05:06:07

postgres_verbose

This style matches the output of Postgres releases prior to 8.4 when the DateStyle parameter was set to non-ISO output.

SET intervalstyle = 'postgres_verbose';
SELECT make_interval(1, 2, 3, 4, 5, 6, 7);

Result:

@ 1 year 2 mons 25 days 5 hours 6 mins 7 secs

iso_8601

This style matches the “format with designators” described in section 4.4.3.2 of the ISO 8601 standard.

SET intervalstyle = 'iso_8601';
SELECT make_interval(1, 2, 3, 4, 5, 6, 7);

Result:

P1Y2M25DT5H6M7S