Using postgres "time with timezone" with sqlalchemy

Question

I use postgres and sqlalchemy with python 3.7

I've got a column with type "time with timezone" and try to insert a new line.

TAB = Table("tab", METADATA,
           # ...

           Column('created', DateTime(timezone=True), default=datetime.now)
           )

Now I try to insert a new line. I got this message:

(psycopg2.ProgrammingError) column "created" is of type time with time zone but expression is of type timestamp without time zone

Has anybody an idea what I have to do?

Thanks!

Maybe datetime.utcnow(), or as in this answer: https://stackoverflow.com/a/33532154/5781248 — J.J. Hakala, Feb 21 '19 at 00:30
Possible duplicate of [SQLAlchemy default DateTime](https://stackoverflow.com/questions/13370317/sqlalchemy-default-datetime) — J.J. Hakala, Feb 21 '19 at 00:31

benvc · Answer 1 · 2019-08-06T16:13:48.853

The primary issue seems to be that you are mapping a SQLALchemy DateTime column to a PostgreSQL time with time zone column. You should look into Time instead.

If you meant to use a PostgreSQL timestamp with time zone column, then you still need to rethink your default setting since datetime.now is not time zone aware. There are a few ways to handle this, but a simple one is to use SQLAlchemy's func.now() instead. For example:

from sqlalchemy import Column, DateTime, Integer, MetaData, Table
from sqlalchemy.sql import func

metadata = MetaData()

example = Table('example', metadata,
    Column('id', Integer, primary_key=True),
    Column('date', DateTime(timezone=True), default=func.now())
    )

Using postgres "time with timezone" with sqlalchemy

1 Answers1