postgres cast | convert interval to iso_8601 format

Question

In pg month could be 'mon' or 'month' in php 'mon' is for monday.

myinterval is a interval column on the db.

how to make pg output

`SELECT myinterval FROM table` = 1 year 6 mons

to

`SELECT myinterval FROM table` = P1Y6M

I read about intervalstyle but I'm working with existing code so I can't mess with some lines, with intervalstyle it will change for the whole session

Answer 1

Sorry for answering this question so late, but I just encountered this same issue in my legacy code base. I solved this by using a transaction and setting the interval style for the duration of the transaction:

BEGIN;

SET LOCAL intervalstyle = 'iso_8601';
SELECT (INTERVAL '6 years 5 months 4 days 3 hours 2 minutes 1 second')::text;

COMMIT;

This outputs P6Y5M4DT3H2M1S

If I run this directly afterwards:

SELECT (INTERVAL '6 years 5 months 4 days 3 hours 2 minutes 1 second')::text;

Then I get 6 years 5 mons 4 days 03:02:01

Therefore the session's interval style isn't affected.