CODEWITHSUNDEEP
pythondatetimesqlalchemy
sirrocco
sirrocco

Reputation: 8055

SqlAlchemy converting UTC DateTime to local time before saving

I have the following situation: - Postgres backend with a field 

timestamp without time zone

So in the database the date stored is no longer 6AM .. but 9AM - it's converted in my local timezone.

I don't understand what's happening. Why is the saved date converted to local ?

Thanks.

Edit

So after @univerio's reply I tried something: I removed the tzinfo from the date time by doing 

.replace(tzinfo = None)

And now the date is saved correctly - it doesn't adjust it to the local time. I don't quite understand why so I'll leave the question open for now in case someone has an explanation.

Thanks.

Upvotes: 20

Views: 16739

Answers (3)

BentHam
BentHam

Reputation: 161

Like yespbs, I also wasn't able to resolve this issue on my stack (postgresql+psycopg2 backend) using the -c timezone=utc connect arg solution above. Their init_command connect arg didn't work either (not supported by PSQL it seems). The solution for me was to use the fix described in this similar SO issue; namely, to execute a command to set the session time zone. In my session builder helper, I have:

if not engine:
    sqlalchemy_db_url = get_db_url(config)
    engine = create_engine(sqlalchemy_db_url)
session = Session(autocommit=False, autoflush=False, bind=engine)
# Make postgres return UTC datetimes, instead of converting to local time
if config.db_backend.startswith("postgresql"):
    session.execute(text("SET TIME ZONE 'UTC';"))
return session

It's worth noting that this SET TIME ZONE '<abbrev>' syntax is a PSQL extension and isn't supported by other database backends (hence the if guard checking the backend starts with "postgresql"). Ref

Upvotes: 0

yespbs
yespbs

Reputation: 440

This was helpful, but while using flask and mysql+pymysql or mysql+mysqldb only thing that worked

{"connect_args": {"init_command": "SET time_zone = '+00:00'"}} you can check engine params in appropiate driver connections i.e. site-packages/pymysql/connections.py

Upvotes: 2

univerio
univerio

Reputation: 20508

What I suspect is happening is that you are storing aware datetimes correctly, but are not reading it back with a time zone because the column is WITHOUT TIME ZONE. Each PostgreSQL connection has an associated time zone that defaults to the system's time zone, so when you retrieve a particular TIMESTAMP it gets returned as a naïve datetime in the system's time zone. For this reason, I always recommend storing TIMESTAMP WITH TIME ZONE instead.

If you want to change the time zone of the connection in SQLAlchemy to UTC, do the following when you create the engine:

engine = create_engine("...", connect_args={"options": "-c timezone=utc"})

This should make you read the value back as a naïve datetime in UTC.

EDIT: @Peter The documentation does not make it obvious how to do this; I had to read several different docs and connect the dots:

  1. the SQLAlchemy documentation about connect_args that allows you to pass arguments directly to the DBAPI connect()
  2. the psycopg2 documentation on connect, which tells you about the extra parameters you can pass to libpq
  3. the libpq documentation on the options parameter that allows you to pass command-line options when connecting with libpq
  4. the the PostgreSQL documentation about the -c command-line switch that allows you to modify config settings
  5. finally, the PostgreSQL client documentation about the timezone client setting that you can set

Upvotes: 59

Related Questions