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.

Answer 3

The fact that Postgresql is capable of storing timestamps with the timezone is a good thing. A timestamp is supposed to represent a point in time. If you read "2013-03-15 14:38:00", is it sufficient to know exactly what point in time is represented? No! It could be 14:38 in New York or 14:38 in Berlin, and these are two different points in time. So there really should never be timestamps without timezones (they are called "naive" datetimes), unless a specific timezone is implied (for example UTC).

The fact that Django creates a timestamp with timezone is therefore a good thing, in my opinion. You can override that default behavior by writing your own DatetimeField, but I wouldn't recommend it, unless you have very good reason to get rid of timezones in the database (because it breaks some other legacy program, for example).

If you use Django < 1.4 then the postgresql client code assumes that all datetimes are in your system's timezone. So if your django server's timezone is Berlin, then "2013-03-15 14:38:00" will be interpreted as 14:38 in Berlin timezone, and all that information will be stored in the database. If you remove the time zone information, then you will have problems at DST time changes. For example, "2013-10-26 02:30:00" is ambiguous: is it the first or second occurrence of 2:30am? If you go that route, you must convert all datetimes to/from UTC in your Django code, and store UTC datetimes in the database.

If you use Django >= 1.4, then by default it is timezone-aware and expects every datetime to have a timezone. Since python datetimes unfortunately do not have timezones by default, you should use the pytz package to make sure every datetime you manipulate is timezone-aware.

The Django documentation about timezones is a very good read.