Can't subtract offset-naive and offset-aware datetimes using bindparam

[hrist0stoichev]

• asyncpg version: 0.23.0
• PostgreSQL version: postgres:11-alpine
• Do you use a PostgreSQL SaaS? If so, which? Can you reproduce
  the issue with a local PostgreSQL install?: I'm using docker image on a local machine
• Python version: 3.7.5
• Platform: mac
• Do you use pgbouncer?: no
• Did you install asyncpg with pip?: yes
• If you built asyncpg locally, which version of Cython did you use?:
• Can the issue be reproduced under both asyncio and
  uvloop?: I guess the event loop doesn't really matter here

I have the following issue where I try to use bindparam to include a datetime with tzinfo in a select statement and then use it to insert in another table. This is a simplified version of the code (tested that it actually produces the error):

sel = select([User.id, bindparam("timestamp", datetime.datetime.utcnow().replace(tzinfo=datetime.timezone.utc))])
ins = insert(DeletedUser).from_select([DeletedUser.id, DeletedUser.timestamp], sel)
await conn.execute(ins)

which results in

sqlalchemy.exc.DBAPIError: (sqlalchemy.dialects.postgresql.asyncpg.Error) <class 'asyncpg.exceptions.DataError'>: invalid input for query argument $1: datetime.datetime(2019, 5, 16, 2, 49, 55... (can't subtract offset-naive and offset-aware datetimes)

DeletedUser.timestamp is of type timestamp with timezone info which I manually verified.

The following code works without any issues:

await conn.execute(insert(DeletedUser).values(id=5, timestamp=datetime.datetime.utcnow().replace(tzinfo=datetime.timezone.utc)))

Am I doing something wrong?

[hrist0stoichev]

OK, I managed to reproduce it with even more minimal example. It looks like the issue is not in the insert but in the select itself.

sel = select([User.id, bindparam("timestamp", datetime.datetime.utcnow().replace(tzinfo=datetime.timezone.utc))])
await conn.execute(sel)

[elprans]

This is the appropriate way of getting a tz-aware current datetime: datetime.now(timezone.utc). Don't use utcnow()

[hrist0stoichev]

@elprans Tried it like that but still produces the same error

sel = select([User.id, bindparam("timestamp", datetime.datetime.now(datetime.timezone.utc))])
await conn.execute(sel)

[elprans]

It would be helpful if you could attach the generated SQL here and the actual list of arguments passed. I suspect it's a SQLAlchemy bug, not asyncpg's

[hrist0stoichev]

Here's the actual SQL and parameters.

sqlalchemy.exc.DBAPIError: (sqlalchemy.dialects.postgresql.asyncpg.Error) <class 'asyncpg.exceptions.DataError'>: invalid input for query argument $1: datetime.datetime(2021, 7, 29, 9, 33, 20... (can't subtract offset-naive and offset-aware datetimes)
[SQL: SELECT user.id, %s AS anon_1 
FROM users]
[parameters: (datetime.datetime(2021, 7, 29, 9, 33, 20, 663120, tzinfo=datetime.timezone.utc),)]

I think it's related to #138

[elprans]

That doesn't look like the final SQL, since it still has the argument placeholder. I suspect that SELECT is missing the correct argument type specifier, it should be: SELECT user.id, $1::timestamptz AS anon_1

[hrist0stoichev]

Finally made it work... I guess sqlalchemy is just not inferring the type correctly. Sorry for opening the issue.

from sqlalchemy.types import TIMESTAMP

sel = select([User.id, bindparam("timestamp", datetime.datetime.now(datetime.timezone.utc), type_=TIMESTAMP(timezone=True))])
await conn.execute(sel)

[plankthom]

Bumped into this issue while migrating to async sqlalchemy on asyncpy:

Had to change models with field declaration

from sqlalchemy import Column, String, DateTime
class AuditMixin:
    user_created = Column(String)
    timestamp_created = Column(DateTime)
    user_modified = Column(String)
    timestamp_modified = Column(DateTime)

to

from sqlalchemy import Column, String
from sqlalchemy.types import TIMESTAMP

class AuditMixin:
    user_created = Column(String)
    timestamp_created = Column(type_=TIMESTAMP(timezone=True))
    user_modified = Column(String)
    timestamp_created = Column(type_=TIMESTAMP(timezone=True))

[plankthom]

@elprans Should this then reported as a sqlalchemy issue?

[jhomsm]

Should this then reported as a sqlalchemy issue?

Looks like SQLAlchemy is well aware of this, as documentation is quite explicit:

In sqlalchemy.types.DateTime

For timezone support, use at least the TIMESTAMP datatype, if not the dialect-specific datatype object.

Also, in sqlalchemy.types.TIMESTAMP

TIMESTAMP datatypes have support for timezone storage on some backends, such as PostgreSQL and Oracle. Use the TIMESTAMP.timezone argument in order to enable “TIMESTAMP WITH TIMEZONE” for these backends