Problem using custom DateTime type with Postgres

I haven't used SqlAlchemy much beyond the Flask-SqlAlchemy API, so apologies if this is trivial--and not a bug.

The issue occurs when filtering by a datetime field, which uses a Custom Type UTCDateTime. I can reproduce the problem on Postgres but not with SQLite.

postgres version: PostgreSQL 9.4.5 on x86_64-apple-darwin14.5.0, compiled by Apple LLVM version 7.0.0 (clang-700.0.72), 64-bit

tested sqlalchemy versions: 1.0.9, SQLAlchemy==1.1.0b1.dev0 with psycopg2==2.6.1

from datetime import datetime, timedelta
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import types
from pytz import utc

# setup typical flask-sqlalchemy enabled app
app = Flask(__name__)
app.config.update(
  SQLALCHEMY_DATABASE_URI = 'postgresql://taylop53:@localhost:5432/test',
  SQLALCHEMY_ECHO = False,
  SECRET_KEY = 'a-secret-key',
  DEBUG = True
)
db = SQLAlchemy(app)

# custom datetime type
class UTCDateTime(types.TypeDecorator):
    """ decorator to enforce tz aware DateTime.
        http://stackoverflow.com/questions/79797/how-do-i-convert-local-time-to-utc-in-python
    """
    impl = types.DateTime

    def process_bind_param(self, value, engine):
        if value is not None:
            return value.astimezone(utc)

    def process_result_value(self, value, engine):
        if value is not None:
            return value.replace(tzinfo=utc)

# model that uses UTCDateTime
class EventUTC(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    timestamp = db.Column(UTCDateTime)

    def events_before(self):

        return EventUTC.query.filter(
            (EventUTC.timestamp <= self.timestamp)
        ).all()


def create_sample_events():
    db.drop_all()
    db.create_all()
    # create some utc events
    now = datetime.utcnow().replace(tzinfo=utc)
    events = [
        EventUTC(timestamp=now + timedelta(minutes=t))
        for t in range(-2, 1)
    ]
    for ev in events: db.session.add(ev)
    db.session.commit()


def check_timestamp_is_before(candidate, timestamp):
    print 'evaluating: ', candidate.timestamp, ' <= ', timestamp
    if not (candidate.timestamp <= timestamp):
        print '^^^ failed'

def test_filter_query(event):
    events_before =  event.events_before()
    print 'test for event with timestamp: ', event.timestamp
    for test_ev in events_before:
        check_timestamp_is_before(test_ev, event.timestamp)
    print '\n'


if __name__ == '__main__':
    create_sample_events()
    all_utc_event = EventUTC.query.all()

    # 'for each test event,'
    # 'fetch all events that occur before (or at the same time)'
    # 'Using tz aware datetimes'
    # 'fails when using postgres, succeeds for sqlite'
    map(test_filter_query, all_utc_event)

I'm using a TypeDecorator to enforce UTC timezoneaware datetimes, instead of using the standard DateTime type.

This works with postgres when simply storing and accessing the datetime field.

However, it behaves unexpected when filtering on the datetime field.

Comments (7)