DEFAULT NOW() WITH TIME ZONE 'UTC' is getting ignored, thus returning a naive datetime when queried.

[Isaglish]

• asyncpg version: 0.27.0
• PostgreSQL version: 15.1
• Do you use a PostgreSQL SaaS? If so, which? Can you reproduce the issue with a local PostgreSQL install?: No
• Python version: 3.11.1 and 3.10.1
• Platform: Windows 11 or PC
• 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?: Probably, never tried.

DEFAULT NOW() WITH TIME ZONE 'UTC' is not getting parsed to datetime.datetime with tzinfo. It basically ignores the tzinfo and returns a naive datetime when queried. Inserting an aware datetime is fine though.

[elprans]

Not sure I understand the issue. Can you post a repro example please? What is the column datatype the default is attached to?

[Isaglish]

Uhh, when creating a table with say:

CREATE TABLE IF NOT EXISTS testing_table (
id SERIAL PRIMARY KEY,
registered_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() AT TIME ZONE 'UTC'
);

And then when you try to get the value of this through Python, it returns a naive datetime (returns a datetime.datetime object without the tzinfo) so it basically doesn't get formatted.

So basically you have to insert the value yourself into the table rather than relying on the DEFAULT keyword to do its job when you insert a value into the table.

[Isaglish]

Just discussed this issue again in the discord.py server, they said it was a syntax error. I was doing DEFAULT NOW() AT TIME ZONE 'UTC'; when it was supposed to be wrapped in parenthesis DEFAULT (NOW() AT TIME ZONE 'UTC');

But it wasn't giving me any syntax error, instead it was giving me a naive datetime object, here is an example:

r = await bot.pool.fetchrow("SELECT NOW() AT TIME ZONE 'America/Los_Angeles'")
return r[0], r[0].tzinfo

# this would return
(datetime.datetime(2023, 2, 14, 4, 9, 45, 68841), None)