Why does Django create Postgres timestamp columns with time zones?

Question

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?

Answer 1

The bad news is that the root of the problem is in Python's datetime implementation.

The good news is that Django has an open ticket on this problem.

The bad news is that the ticket was opened in 2006.

The good news is that a recent proposal is more useful reading and seems to be in development. The thread containing the proposal is long, but very informative.

The bad news is that proposal boils down to "this is a real mess". (Still, it's in development.)

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

No, that's a Django design decision. PostgreSQL stores only UTC; it doesn't store the timezone, and it doesn't store the offset. The timezone is conceptually like an environmental variable that gets applied to timestamps as they're inserted or selected for retrieval.

From the django developer's mail archive . . .

In the absence of actual time zone support in the database, any action taken by django is going to inconvenience somebody, and will likely not be compatible with non-django use of the same database.

That's a big problem--a fix that might make the database incompatible with other languages or frameworks. That's an absolute show-stopper where I work; many programming languages and frameworks access the database.

SQLite, Microsoft Access, and MySQL (datetime data type, not timestamp) are cited in that thread as lacking timezone support in the database.

Answer 2

I think you have an incorrect assumption. PostgreSQL does not store time zone information:

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 actually, the time zone information is lost. But the instant of time is preserved.

Meaning that when you get the timestamp information, whatever time zone you or your server currently are, will always be correct. For most applications, this is exactly what you want.