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?

Solution

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. =)

Comments

[ThiefMaster]

unrelated, but to future readers: you want default=func.now without the () or the default value will be the time when the file was imported (e.g. when the server was started in case of a webapp), and not the current time when the row was inserted

[orange]

I don't want to store timezone information. The system should be timezone agnostic and func.now() should behave the same way as initialising created_on=datetime.datetime.now().

[orange]

I think timestamp without time zone AT TIME ZONE zone may be a better fit which seems to respect local time: postgresql.org/docs/current/static/…

[orange]

Even better, set the target timezone timestamps are converted to as explained here: serverfault.com/questions/554359/…. This wouldn't require any code changes.