# Range data types

SQLAlchemy-Utils provides wide variety of range data types. All range data types return Interval objects of intervals package. In order to use range data types you need to install intervals with:

    pip install intervals

Intervals package provides good chunk of additional interval operators that for example psycopg2 range objects do not support.

Some good reading for practical interval implementations:

http://wiki.postgresql.org/images/f/f0/Range-types.pdf

## Range type initialization

    from sqlalchemy_utils import IntRangeType

    class Event(Base):
        __tablename__ = 'user'
        id = sa.Column(sa.Integer, autoincrement=True)
        name = sa.Column(sa.Unicode(255))
        estimated_number_of_persons = sa.Column(IntRangeType)

You can also set a step parameter for range type. The values that are not multipliers of given step will be rounded up to nearest step multiplier.

    from sqlalchemy_utils import IntRangeType

    class Event(Base):
        __tablename__ = 'user'
        id = sa.Column(sa.Integer, autoincrement=True)
        name = sa.Column(sa.Unicode(255))
        estimated_number_of_persons = sa.Column(IntRangeType(step=1000))

    event = Event(estimated_number_of_persons=[100, 1200])
    event.estimated_number_of_persons.lower  # 0
    event.estimated_number_of_persons.upper  # 1000

## Range type operators

SQLAlchemy-Utils supports many range type operators. These operators follow the intervals package interval coercion rules.

So for example when we make a query such as:

    session.query(Car).filter(Car.price_range == 300)

It is essentially the same as:

    session.query(Car).filter(Car.price_range == DecimalInterval([300, 300]))

### Comparison operators

All range types support all comparison operators (>, >=, ==, !=, <=, <).

    Car.price_range < [12, 300]

    Car.price_range == [12, 300]

    Car.price_range < 300

    Car.price_range > (300, 500)

    # Whether or not range is strictly left of another range
    Car.price_range << [300, 500]

    # Whether or not range is strictly right of another range
    Car.price_range >> [300, 500]

### Membership operators

    Car.price_range.contains([300, 500])

    Car.price_range.contained_by([300, 500])

    Car.price_range.in_([[300, 500], [800, 900]])

    ~ Car.price_range.in_([[300, 400], [700, 800]])

### Length

SQLAlchemy-Utils provides length property for all range types. The implementation of this property varies on different range types.

In the following example we find all cars whose price range’s length is more than 500.

    session.query(Car).filter(
        Car.price_range.length > 500
    )

## DateRangeType

class `sqlalchemy_utils.types.range.``DateRangeType`(*args, **kwargs)

  DateRangeType provides way for saving ranges of dates into database. On PostgreSQL this type maps to native DATERANGE type while on other drivers this maps to simple string column.

  Example:

      from sqlalchemy_utils import DateRangeType

      class Reservation(Base):
          __tablename__ = 'user'
          id = sa.Column(sa.Integer, autoincrement=True)
          room_id = sa.Column(sa.Integer))
          during = sa.Column(DateRangeType)

## DateTimeRangeType

class `sqlalchemy_utils.types.range.``DateTimeRangeType`(*args, **kwargs)

## IntRangeType

class `sqlalchemy_utils.types.range.``IntRangeType`(*args, **kwargs)

  IntRangeType provides way for saving ranges of integers into database. On PostgreSQL this type maps to native INT4RANGE type while on other drivers this maps to simple string column.

  Example:

      from sqlalchemy_utils import IntRangeType

      class Event(Base):
          __tablename__ = 'user'
          id = sa.Column(sa.Integer, autoincrement=True)
          name = sa.Column(sa.Unicode(255))
          estimated_number_of_persons = sa.Column(IntRangeType)

      party = Event(name=u'party')

      # we estimate the party to contain minium of 10 persons and at max
      # 100 persons
      party.estimated_number_of_persons = [10, 100]

      print party.estimated_number_of_persons
      # '10-100'

  IntRangeType returns the values as IntInterval objects. These objects support many arithmetic operators:

      meeting = Event(name=u'meeting')

      meeting.estimated_number_of_persons = [20, 40]

      total = (
          meeting.estimated_number_of_persons +
          party.estimated_number_of_persons
      )
      print total
      # '30-140'

## NumericRangeType

class `sqlalchemy_utils.types.range.``NumericRangeType`(*args, **kwargs)

  NumericRangeType provides way for saving ranges of decimals into database. On PostgreSQL this type maps to native NUMRANGE type while on other drivers this maps to simple string column.

  Example:

      from sqlalchemy_utils import NumericRangeType

      class Car(Base):
          __tablename__ = 'car'
          id = sa.Column(sa.Integer, autoincrement=True)
          name = sa.Column(sa.Unicode(255)))
          price_range = sa.Column(NumericRangeType)

## RangeComparator

class `sqlalchemy_utils.types.range.``RangeComparator`(expr)