Postgres + SQLAlchemy converts time to UTC when using default = func.now ()

Question

I have a SQLAlchemy table that is defined

foo_table = Table('foo', metadata, Column('id', Integer, primary_key=True), Column('created_on', DateTime, default=func.now()), ... ) 

which creates a table in Postgres

 CREATE TABLE foo ( id serial NOT NULL, created_on timestamp without time zone, ... ) 

The local time zone is set correctly (it is verified that Python datetime.datetime.now() displays my local time), but whenever I insert a line into foo_table without explicitly setting created_on , the time is used for the current UTC instead of my local time (for example, "2015-07-29 16: 38: 08.112192").

When I try to set created_on manually, it all works fine, but Postgres or SQLAlchemy seems to convert the time to UTC, leaving it to func.now() to assign a timestamp.

How can I get SQLAlchemy or Postgres to simply create a record with my current local time?

Answer 1

The key part here is that the created_on column is defined as a “timestamp without a time zone”.

From the documentation here:

http://www.postgresql.org/docs/current/static/datatype-datetime.html

In literature that has been defined as a timestamp without a time zone, PostgreSQL silently ignores any time zone indication. That is, the resulting value is derived from the date / time fields in the input value and is not adjustable for the time zone.

For a timestamp with a time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally called Greenwich Mean Time, GMT). The input value that the specified explicit time zone has is converted to UTC using the appropriate offset for this time zone. If the time zone is not specified in the input line, it is assumed that it is in the time zone specified by the TimeZone parameter of the system and converted to UTC using the offset for the time zone zone.

This way, you delete time zone information that only leaves you with a saved UTC date.

In the case of SQLAlchemy, to create a timestamped column with a time zone, you need to add something to the effect:

DateTime (time zone = True)

according to the documentation here:

http://docs.sqlalchemy.org/en/rel_0_9/core/type_basics.html

This will allow you to save the time zone information you want and retrieve it as well.

Hope that helps answer your question and helps you. Good luck. =)