SqlAlchemy converting UTC DateTime to local time before saving

Question

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

timestamp without time zone

• Right before saving the datetime value, it looks like : 2014-09-29 06:00:00+00:00
• I then load the same row from the db and the value is : 2014-09-29 09:00:00

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.

Answer 1

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