How to add explicit types so I can use OVERLAPS?

Matthew Wilson <[email protected]>
Newsgroups gmane.comp.python.db.psycopg.devel
Message-ID <[email protected]>
I want to use the OVERLAPS keyword to compare two time intervals, but
I'm having trouble.

In the code below, I want to test if the time interval from 9:15 AM
through 9:45 AM overlaps the time interval from 8 AM through 10 AM.

This approach fails:

    >>> d = {'9:15 AM':datetime(2008, 8, 1, 9, 15),
    ...      '9:45 AM':datetime(2008, 8, 1, 9, 45),
    ...      '8 AM':datetime(2008, 8, 1, 8),
    ...      '10 AM':datetime(2008, 8, 1, 10)}

    >>> cursor.execute('select (%(9:15 AM)s, %(9:45 AM)s) '
    ...                'overlaps (%(8 AM)s, %(10 AM)s);', d)

    ------------------------------------------------------------
    Traceback (most recent call last):
      File "<ipython console>", line 1, in <module>
    ProgrammingError: function pg_catalog.overlaps(unknown, unknown,
    unknown, unknown) is not unique
    LINE 1: ...ct ('2008-08-01T09:15:00', '2008-08-01T09:45:00') overlaps
    (...
                                                                 ^
    HINT:  Could not choose a best candidate function. You might need to add
    explicit type casts.

Meanwhile, this approach works fine:

    >>> cursor.execute('select (TIMESTAMP %(9:15 AM)s, TIMESTAMP %(9:45 AM)s) '
                       'overlaps (TIMESTAMP %(8 AM)s, TIMESTAMP %(10 AM)s);', d)

    >>> cursor.fetchall()
    [(True,)]

For a lot of boring reasons, I don't want to go into all my code and add
the TIMESTAMP cast.

Is there anything else possible?

I'm happy to submit a patch if that is what it would take to add this
feature, and the patch will be considered.

Matt
lmpx.com only provides a reader for public news (NNTP) servers. It is not affiliated with the servers or forums shown here and is not responsible for the content of articles, which is written by their respective authors.