Asyncpg: error passing timezone aware datetimes

[samuelcolvin]

• asyncpg version: 0.10.1
• PostgreSQL version: 9.6
• Python version: 3.6
• Platform: Ubuntu
• Do you use pgbouncer?: no
• Did you install asyncpg with pip?: yes
• If you built asyncpg locally, which version of Cython did you use?: no
• Can the issue be reproduced under both asyncio and uvloop?: yes

code:

async def test_timezone(db_conn):
    v1 = datetime.now()
    print(v1)
    print(await db_conn.fetchval('SELECT $1::TIMESTAMP', v1))

    v2 = v1.replace(tzinfo=timezone.utc)
    print(v2)
    print(await db_conn.fetchval('SELECT $1::TIMESTAMP', v2))

The first query works fine, but the second gives an error "TypeError: can't subtract offset-naive and offset-aware datetimes". Same happens if I use a pytz.timezone(...) timezone.

I would have assumed from other issues here and docs that timezone aware datetimes should work.

pytest output with traceback:

Test session starts (platform: linux, Python 3.6.0, pytest 3.0.7, pytest-sugar 0.8.0)
rootdir: /home/samuel/imber/em2, inifile: setup.cfg
plugins: xdist-1.15.0, toolbox-0.2, sugar-0.8.0, mock-1.5.0, isort-0.1.0, cov-2.4.0, aiohttp-0.1.3


―――――――――――――――――――――――――――――――――――――――――――――― test_timezone[loop0] ―――――――――――――――――――――――――――――――――――――――――――――――

db_conn = <asyncpg.connection.Connection object at 0x7f862db6f708>

    async def test_timezone(db_conn):
        v1 = datetime.now()
        print(v1)
        print(await db_conn.fetchval('SELECT $1::TIMESTAMP', v1))

        v2 = v1.replace(tzinfo=timezone.utc)
        print(v2)
>       print(await db_conn.fetchval('SELECT $1::TIMESTAMP', v2))

tests/test_domestic.py:99: 
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
env/lib/python3.6/site-packages/asyncpg/connection.py:357: in fetchval
    data = await self._execute(query, args, 1, timeout)
env/lib/python3.6/site-packages/asyncpg/connection.py:651: in _execute
    return await self._do_execute(query, executor, timeout)
env/lib/python3.6/site-packages/asyncpg/connection.py:672: in _do_execute
    result = await executor(stmt, None)
asyncpg/protocol/protocol.pyx:162: in bind_execute (asyncpg/protocol/protocol.c:57595)
    ???
asyncpg/protocol/prepared_stmt.pyx:122: in asyncpg.protocol.protocol.PreparedStatementState._encode_bind_msg (asyncpg/protocol/protocol.c:53724)
    ???
asyncpg/protocol/codecs/base.pyx:150: in asyncpg.protocol.protocol.Codec.encode (asyncpg/protocol/protocol.c:13906)
    ???
asyncpg/protocol/codecs/base.pyx:95: in asyncpg.protocol.protocol.Codec.encode_scalar (asyncpg/protocol/protocol.c:13291)
    ???
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

>   ???
E   TypeError: can't subtract offset-naive and offset-aware datetimes

asyncpg/protocol/codecs/datetime.pyx:118: TypeError
---------------------------------------------- Captured stdout call -----------------------------------------------
2017-05-11 17:57:07.130524
2017-05-11 17:57:07.130524
2017-05-11 17:57:07.130524+00:00

 tests/test_domestic.py ⨯                                                                           100% ██████████

Results (0.45s):
       1 failed
         - tests/test_domestic.py:92 test_timezone[loop0]

[elprans]

You are mixing timezone-naive and timezone-aware types. Use timestamptz.

We could certainly improve the handling of this, though.

[samuelcolvin]

thanks, sorry - my mistake.

[hanchang]

Leaving this for posterity since it wasn't immediately obvious to me -

the issue is samuelcolvin's PostgreSQL column is of type TIMESTAMP which is NOT timezone aware, so either the column needs to be modified to TIMESTAMPTZ which IS timezone aware OR the Python datetime object he's trying to pass in needs to be made naive and also not timezone aware.

Hopefully this helps a poor soul in the future.