Filter on/Order by Postgres range type in SQLAlchemy
Question: SQLAlchemy supports Postgres range types, as described
[here](http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#range-
types). It uses the `postgresql+psycopg2` dialect for Postgres communication.
[These
testcases](https://bitbucket.org/zzzeek/sqlalchemy/src/55eacc8dbea3c3f98197bde9034fd6558fb2bc09/test/dialect/postgresql/test_types.py?at=master#cl-1378)
give usage examples for the range types in SQLALchemy.

How can I filter by, or order by, one component (lower or upper) of such a
range field in SQLAlchemy?

Using the example from the first link

    
    
    from psycopg2.extras import DateTimeRange
    from sqlalchemy.dialects.postgresql import TSRANGE
    
    class RoomBooking(Base):
    
      __tablename__ = 'room_booking'
    
      room = Column(Integer(), primary_key=True)
      during = Column(TSRANGE())
    
    booking = RoomBooking(
      room=101,
      during=DateTimeRange(datetime(2013, 3, 23), None)
    )
    

I would, e.g., like to filter on bookings with a during that begins on a given
datetime or order the bookings by the start of the datetime.

As such I'm looking to generate roughly this SQL:

    
    
    SELECT room, during
    FROM room_booking
    WHERE lower(during) = foo
    ORDER BY upper(during)
    

I have tried constructs like

    
    
    RoomBooking.query.filter(RoomBooking.during.lower == foo).order_by(RoomBooking.during.upper)
    

but recognize that this is likely not working because lower is an attribute on
the python object and not associated with the underlying table column.

One possible solution to this might be finding a way to use the
[`upper()/lower()`](http://www.postgresql.org/docs/9.2/static/functions-
range.html#RANGE-FUNCTIONS-TABLE) range functions from SQLAlchemy.


Answer: One way to do this is to use the already existing `func.lower()/func.upper()`
methods in sqlalchemy:

    
    
    from sqlalchemy import func
    RoomBooking.query.filter(func.lower(RoomBooking.during) == foo).order_by(func.upper(RoomBooking.during))
    

These methods were probably introduced to support (un)capitalizing text – it
would be interesting to see if other unavailable postgres functions can be
implemented in a similar manner as well.

