Why does Django create Postgres timestamp columns with time zones?

[Kevin]

I thought that Django created datetime columns that were time zone agnostic, but when I looked at my Postgres table I saw that the values recorded there have time zone information.

Going further I found that the Postgres backend directs Django to create columns that use time zones.

From django/db/backends/postgresql/creation.py:

    data_types = {

            ...

            'DateTimeField':     'timestamp with time zone',

            ...

The schema shows that the created column is specified as "timestamp with time zone".

    CREATE TABLE notification_notice

    (

      ...

      created timestamp with time zone NOT NULL,

      ...

The Postgres log shows the update statement that was sent. Django constructed a SQL statement that used UTC as the time zone as directed by my Django settings file.

    UPDATE "notification_notice" SET "sender_id" = 1, "group_id" = NULL, "notice_type_id" = 1, "content_type_id" = 21, "object_id" = 3, "created" = E'2011-11-11 22:31:08.022148' WHERE "notification_notice"."id" = 14

This is what my table looks like. The created column has a timestame that has "-08" for its time zone. Postgres must be inspecting the time zone of my system clock to find the time zone.

    my_db=# select * from notification_notice limit 1;

     id | sender_id | group_id | notice_type_id | content_type_id | object_id |           created            | last_interaction_time 

    ----+-----------+----------+----------------+-----------------+-----------+------------------------------+-----------------------

      1 |           |        3 |             21 |              53 |         6 | 2011-11-11 14:31:02.98882-08 | 

    (1 row)

Questions:

Doesn't Django have a hands off policy to time zones?

Why does the Postgres backend use time zones for models.DateTimeField? Is this required by Postgres?

Is there a way to force Django to create timestamp columns in Postgres that don't use the time zone?

[K.C. Smith]

It's not required by PostgreSQL. See,
http://www.postgresql.org/docs/8.4/interactive/datatype-datetime.html
, for example.

You may be able to change the data type of that column directly in the
database. (See http://www.postgresql.org/docs/8.4/interactive/ddl-alter.html#AEN2595
.)

Maybe "ALTER TABLE notification_notice ALTER COLUMN created TYPE
timestamp without time zone ;"

K.C.

[Kevin]

From postgresql.org/docs/8.4/static/datatype-datetime.html: "All timezone-aware dates and times are stored internally in UTC. They are converted to local time in the zone specified by the timezone configuration parameter before being displayed to the client."

So I guess all that is needed is

SET TIME ZONE 'UTC';

Problem solved.