Conversion problem between interval and timedelta

[lelit]

I'm using asyncpg 0.11.0 and I hit a problem with a table containing an INTERVAL column.

Consider the following script:

import asyncio
import asyncpg


SETUP_SQL = """\
CREATE TABLE schedules (
  id serial not null primary key,
  name text,
  delay interval
)
"""

INSERT_SQL = """
INSERT INTO schedules (name, delay)
VALUES ('Foobar', '5 years')
"""

FETCH_SQL = """
SELECT delay
FROM schedules
WHERE name = 'Foobar'
"""

CLEANUP_SQL = """
DROP TABLE schedules
"""

async def run():
    conn = await asyncpg.connect(user='lele', password='lele',
                                 database='test', host='127.0.0.1')

    await conn.execute(SETUP_SQL)
    try:
        await conn.execute(INSERT_SQL)
        delay = await conn.fetchval(FETCH_SQL)
        print(delay)
    finally:
        await conn.execute(CLEANUP_SQL)

    await conn.close()


def main():
    loop = asyncio.get_event_loop()
    loop.run_until_complete(run())


if __name__ == '__main__':
    main()

It creates a sample table and inserts one record, with a delay value of five years, and executing it I get:

$ python test.py 
1800 days, 0:00:00

Commenting out the CLEANUP_SQL execution, with psql I get:

test=# select * from schedules;
 id |  name  |  delay  
----+--------+---------
  1 | Foobar | 5 years

and with pgcli, that uses psycopg2, I get:

lele@test> select * from schedules;
+------+--------+--------------------+
|   id | name   | delay              |
+======+========+====================+
|    1 | Foobar | 1825 days, 0:00:00 |
+------+--------+--------------------+

Why am I getting back 1800 days when using asyncpg?

[lelit]

Just for fun I tried to investigate a bit: effectively the interval_decode() function sees the result as “60 months, 0 days, 0 seconds and 0 microsecs”, and that explains the output (computed roughly as 30*months + days).
Then I lost some time trying to find references to how PG serializes its interval data type, with no luck.

OOC, where did you find the details underlying the protocol/codecs.pyx implementation?

[lelit]

I found the details in the PG sources (specifically, in backend/utils/adt/timestamp.c)...

I wonder if it'd make any sense in implementing a more PG-like interval Python data type, something like monthdelta but supporting also day fractions...

[lelit]

This a quick experiment, please let me know if you think it could be acceptable. If so, I could implement remaining logic to allow a (minimal) interoperability with date and datetime.

[lelit]

Please give a look at my interval branch, this commit in particular: it implements most of the mentioned interoperability.

[lelit]

I'm looking forward a review of PR #150 🙏

[elprans]

I'm on vacation until next week. Will review once I'm back. Thanks!

[lelit]

Thank you, have a good time!