[Sqlalchemy-tickets] Issue #3560: Problem using custom DateTime type with Postgres (zzzeek/sqlalchemy)

[Peter T. <iss...@bi...>]

New issue 3560: Problem using custom DateTime type with Postgres
https://bitbucket.org/zzzeek/sqlalchemy/issues/3560/problem-using-custom-datetime-type-with

Peter Taylour:

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.

Behaviour can be reproduced here https://github.com/PTaylour/SqlAlchemy-Postgres-DateTime-Issue/tree/master
(Apologies again for the Flask dependancy)

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`


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

```
class UTCDateTime(types.TypeDecorator):
    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)
``` 

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

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

## In the repo https://github.com/PTaylour/SqlAlchemy-Postgres-DateTime-Issue/
A script that adds a few rows to a table using the SQLalchemy type `DateTime` and a table using the custom type `UTCDateTime`

It then performs a `SELECT` `WHERE` the datetime filed is less than the value of another row.

This performs correctly using sqlite (you can switch to sqlite in the config file `hello.cfg`).

But using a postres database the filter does not perform as I would expect.