DateTime ranges in SQLAlchemy with Postgresql
Postgresql has some very handy features, one of which is the range type. This allows you to express a distinct block of time as a unique value.
Using SQLAlchemy we can quickly use this to add more meaning and cut down on boiler plate.
from sqlalchemy import Column
from sqlalchemy.dialects import postgresql
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class MyModel(Base):
"""My Model"""
__tablename__ = 'mymodel'
id = Column('id', Integer, primary_key=True, nullable=False)
time_slot = Column(postgresql.TSTZRANGE, nullable=False)
This will create a table with an id and a time_slot. For example this would allow us to store the time from 9am - 9:45am without having to store the start and end date separately.
The real power comes when you combine this with some of the Constraints available in Postgresql.
We can create a unique constraint on this range, only allowing there to be 1 slot taken for any particular time. This is done via the __table_args__ property as follows
class MyModel(Base):
"""My Model"""
__tablename__ = 'mymodel'
__table_args__ = (postgresql.ExcludeConstraint(('time_slot', '&&')))
id = Column('id', Integer, primary_key=True, nullable=False)
time_slot = Column(postgresql.TSTZRANGE, nullable=False)
Now you may have noticed the TZ section of this column definition. Here we are using a Time Zone aware datatype. Python does have a somewhat good support for this but they can be quite annoying to work with. A trick I have found is to normalize your dates before passing them to your mapping.
>>> from dateutil import parser
>>> from pytz import utc
>>> d = '2015-01-01 21:00'
>>> timestamp = parser.parse(d)
if not timestamp.tzinfo:
>>> timestamp = utc.localize(timestamp) # Or your timezone of choice
>>> # And use further
(We could use a hybrid_property to simplify this access.)
Finally to use a Time range is as follows
>>> from psqycopg2.extras import DateTimeTZRange
>>> inst = MyModel()
>>> # Take up a 30min slot from `timestamp` onwards
>>> inst.time_slot = DateTimeTZRange(timestamp, timestamp+timedelta(minutes=30))
and we can even query based on them
>>> time_range = DateTimeTZRange(timestamp, timestamp+timedelta(minutes=30))
>>> # find all of the time slots after mine
>>> q = session.query(MyModel).filter(MyModel.time_slot>=time_range).all()