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

Question

I've got 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 timezone is set properly (checked that Python's datetime.datetime.now() displays my local time), but whenever I insert a row in foo_table without explicitly setting created_on, the time used is current UTC instead of my local time (e.g. "2015-07-29 16:38:08.112192").

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

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

Answer 1

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

From the documentation here:

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

In a literal that has been determined to be timestamp without time zone, PostgreSQL will silently ignore any time zone indication. That is, the resulting value is derived from the date/time fields in the input value, and is not adjusted for time zone.

For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system's TimeZone parameter, and is converted to UTC using the offset for the timezone zone.

So, in effect, you're throwing away timezone information, which leaves you with the stored UTC date only.

In the case of SQLAlchemy, to create the column with "timestamp with time zone", you'll need to add something to the effect of:

DateTime(timezone=True)

according to the documentation here:

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

Which should let you save the timezone information you wish, and retrieve it as well.

Hope that helps answer your question and gets you on your way. Best of luck. =)