7

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?

Improve this question
1
  • Possible answer: here Commented Nov 12, 2011 at 0:52

3 Answers 3

Reset to default
17

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Yes I understand that it's a Django design descion. By "Postgres backend", I meant the Postgres backend of the Django ORM.
If the time zone is like an environmental variable, then does that mean I just have to set the time zone (to UTC for example) in my database connexion?
@hekevintran: I'm still reading (again) those references I posted. When I work with timezones and multiple apps, suddenly my name is "Alice", and I'm down the rabbit hole: I'm no longer sure what I do know and what I don't know. I'm going to say, "No", because your Django app would still have to normalize every one of your timestamps to UTC before storing them. Every one of them, forever. I'm also going to say, "No", because I'm pretty sure there are some subtle points to this that I'm missing. Maybe I'm just tired.
I think I have an answer. 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." You can set the time zone you want with SET TIME ZONE 'UTC';.
Why will it corrupt the data? The Django ORM sets the time zone of the Postgres connexion. I use only naive datetime objects and assume that they are in UTC. I set TIME_ZONE to 'UTC'. I have form and template tag code that convert datetime objects between the user's time zone and the server's time zone. The reason I wrote this question was that I was seeing '-08' while querying the table. I didn't realise that that was not the actual data but an effect of the time zone in the connexion. I'm now confused about what you are struggling with. Time zone aware objects? I just avoid them :).
|
5

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.

Improve this answer

Comments

0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.