retrieve INTERVAL columns as ISO 8601 string

[flokli]

Hey,

PostgreSQL stores INTERVAL columns with separate years and months, however, psycopg2 converts these to days, as Python's timedelta doesn't support years and months.

However, sometimes it might be desirable to get the months and years originally stored in the PostgreSQL column, as the length of a months and year depends on the start date. psycopg2 should also provide an interface to simply return the column value as a string in iso_8601 output style, instead of converting to timedelta, so it can be further processed by packages like isodate.

[dvarrazzo]

You can do that registering your own adapter. More specifically you can register the string adapter on the timedelta type.

>>> psycopg2.extensions.register_type(
    psycopg2.extensions.new_type(
        psycopg2.extensions.INTERVAL.values, 'INTERVAL_STR', psycopg2.STRING),
    cur)

>>> cur.execute("select '1 month'::interval")
>>> cur.fetchone()[0]
'1 mon'

>>> cur.execute("set intervalstyle = 'iso_8601'")
>>> cur.execute("select '1 month'::interval")
>>> cur.fetchone()[0]
'P1M'

[flokli]

Awesome, thanks!