Why does Django display all datetimes in UTC by default?

[guillaumechorn@…]

As is mentioned ​here in the docs, Django has time zone aware input in forms, and as a result, PostgreSQL databases store datetime values expressed in the time zone of which the form was aware--not UTC. So why then have Django go through the trouble of constantly converting all datetime values queried from the database to UTC? Why not just make the developer need to explicitly tell Django (either through a simple setting in settings.py, or using an optional function in their views) that they want values expressed in UTC?

The issue with having Django automatically convert all datetime values to UTC, as is the case now, is that in the case that the developer wants datetimes expressed to the user in the same time zone as they were entered by the user (which is quite often, I would imagine), they must actually write code to convert the values back to what is already (!) stored in the database before rendering them in templates. I realize that this is only strictly the case when using PostgreSQL, but it still makes very little sense to me.

The "Time Zone aware output in templates" referred to ​here only works in the most basic invocation of a datetime value (something like {{ object.datetime }}). It doesn't convert the hours if you use something like {{ object.datetime.hour }}, or the day if you use {{ object.datetime.day }}. I find this extremely limited, and quite far removed from Django's usual ability to abstract common tasks. Obviously it's meant to work as an invisible template tag that you don't have to type in yourself, and I understand that the same template tag wouldn't work for both {{ object.datetime }} and {{ object.datetime.day }}. But that's why I think it would make more sense for Django to return datetime values as they are formatted in the database by default, rather than automatically converting them to UTC (at least when usage of PostgreSQL has been declared in settings.py).

Feel free to tell me it's not worth the trouble and I'm being a whiny ingrate, but those are my thoughts. =)

[Aymeric Augustin]

(All my comments assume USE_TZ = True.)

Replying to guillaumechorn@…:

As is mentioned ​here in the docs, Django has time zone aware input in forms, and as a result, PostgreSQL databases store datetime values expressed in the time zone of which the form was aware--not UTC.

This paragraph doesn't talk about PostgreSQL (model layer), it talks about cleaned_data (form layer).

As an implementation detail, PostgreSQL always stores the data as UTC internally, even when USE_TZ = False, and it deals with conversions between UTC and the database connection's time zone when the latter isn't UTC.

When USE_TZ = True, Django converts datetimes to UTC if they're originally in another timezone. (psycopg2 would do it if Django didn't, but it was necessary to implement it in Django to cater for less capable databases.)

So why then have Django go through the trouble of constantly converting all datetime values queried from the database to UTC?

They are in UTC in the database. Keeping them in UTC until another timezone is needed avoids unecessary work.

In hindsight, I reckon that always converting them the the default time zone may have been a better choice, even though it would have been slower.

Why not just make the developer need to explicitly tell Django (either through a simple setting in settings.py, or using an optional function in their views) that they want values expressed in UTC?

You mean, that they want values expressed in the default time zone?

If you want to have everything in local time with no time zone support you can just set USE_TZ = False (and deal with DST issues by yourself).

The issue with having Django automatically convert all datetime values to UTC, as is the case now, is that in the case that the developer wants datetimes expressed to the user in the same time zone as they were entered by the user (which is quite often, I would imagine), they must actually write code to convert the values back to what is already (!) stored in the database before rendering them in templates. I realize that this is only strictly the case when using PostgreSQL, but it still makes very little sense to me.

I don't get why you think it's specific to PostgreSQL. Django provides good cross-database compatibility for something as trivial as datetimes.

The "Time Zone aware output in templates" referred to ​here only works in the most basic invocation of a datetime value (something like {{ object.datetime }}). It doesn't convert the hours if you use something like {{ object.datetime.hour }}, or the day if you use {{ object.datetime.day }}.

Fair enough. Why didn't you bring it up while I was writing the feature? :) Now it's impossible to change :(

I recommend using the |date filter or turning time zone support off.

If only we had a better time zone implementation if Python's stdlib. (In Python 3.5 maybe...)

I find this extremely limited, and quite far removed from Django's usual ability to abstract common tasks.

Thank you.

Obviously it's meant to work as an invisible template tag that you don't have to type in yourself, and I understand that the same template tag wouldn't work for both {{ object.datetime }} and {{ object.datetime.day }}.

I didn't get that part.

But that's why I think it would make more sense for Django to return datetime values as they are formatted in the database by default, rather than automatically converting them to UTC (at least when usage of PostgreSQL has been declared in settings.py).

Like I said above Django *does* return datetime values as stored in the database.

---

To sum up, you misunderstood parts of Django's time zone handling, and I couldn't extract an actionable item from your report. That said, if my comments give you a better picture of Django's behavior, and you have a concrete proposal, please reopen this ticket (or open another one) to suggest changes. Make sure your plan preserves compatibility with existing code.

[anonymous]

Thank you for your quick response.

This paragraph doesn't talk about PostgreSQL (model layer), it talks about cleaned_data (form layer).

I understand that this part doesn't refer specifically to PostgreSQL or the model layer. I just meant that it means the form captures time zone information. I assumed it is this time zone information that is passed on to the database eventually.

As an implementation detail, PostgreSQL always stores the data as UTC internally, even when USE_TZ = False, and it deals with conversions between UTC and the database connection's time zone when the latter isn't UTC.

I missed that part when I originally read the docs, but it doesn't really change my point. From the docs: "In practice, this means it converts datetimes from the connection’s time zone to UTC on storage, and from UTC to the connection’s time zone on retrieval." If PostgreSQL stores datetimes in UTC internally, but translates them to the connection's time zone as they come back out, doesn't that mean Django is receiving those datetimes in the connection's time zone? Because if so, my point is still that it doesn't make sense for Django to then convert those values back to UTC again.

I don't get why you think it's specific to PostgreSQL. Django provides good cross-database compatibility for something as trivial as datetimes.

​This is why. It says right there in the documentation that while PostgreSQL stores time zone information, other database backends don't. That's why my suggestion was for Django to just leave datetimes in whatever format PostgreSQL gives them, unless the developer explicitly chooses otherwise.

Of course, I am basing this on the assumption that Django gets the same data from PostgreSQL as I do when I use psql mydatabase to look at the database directly (for instance, 2013-10-07 01:00:00+08). If that's not true and Django *is* directly accessing the UTC-formatted datetimes stored internally by PostgreSQL, then obviously my point is moot.

Obviously it's meant to work as an invisible template tag that you don't have to type in yourself, and I understand that the same template tag wouldn't work for both {{ object.datetime }} and {{ object.datetime.day }}.

I didn't get that part.

Django automatically converting aware datetime objects to the current time zone in templates is similar to using {% localtime on %} or {{ object.datetime|localtime }}, is it not? My intention was just to say that something like |localtime doesn't work on both {{ object.datetime }} and {{ object.datetime.hour }}, so I get why the current "Time zone aware output in templates" functionality doesn't work for {{ object.datetime.hour }} either. Hence my suggestion to convert datetimes (or not convert them) prior to the template layer.

I guess my concrete proposal would be for Django to *not* automatically convert non-UTC datetimes to UTC when dealing with PostgreSQL, based on my understanding that it gets non-UTC datetimes from PostgreSQL. But as I mentioned above, if that's an incorrect understanding, then case closed.

Replying to aaugustin:

(All my comments assume USE_TZ = True.)

Replying to guillaumechorn@…:

As is mentioned ​here in the docs, Django has time zone aware input in forms, and as a result, PostgreSQL databases store datetime values expressed in the time zone of which the form was aware--not UTC.

This paragraph doesn't talk about PostgreSQL (model layer), it talks about cleaned_data (form layer).

As an implementation detail, PostgreSQL always stores the data as UTC internally, even when USE_TZ = False, and it deals with conversions between UTC and the database connection's time zone when the latter isn't UTC.

When USE_TZ = True, Django converts datetimes to UTC if they're originally in another timezone. (psycopg2 would do it if Django didn't, but it was necessary to implement it in Django to cater for less capable databases.)

So why then have Django go through the trouble of constantly converting all datetime values queried from the database to UTC?

They are in UTC in the database. Keeping them in UTC until another timezone is needed avoids unecessary work.

In hindsight, I reckon that always converting them the the default time zone may have been a better choice, even though it would have been slower.

Why not just make the developer need to explicitly tell Django (either through a simple setting in settings.py, or using an optional function in their views) that they want values expressed in UTC?

You mean, that they want values expressed in the default time zone?

If you want to have everything in local time with no time zone support you can just set USE_TZ = False (and deal with DST issues by yourself).

The issue with having Django automatically convert all datetime values to UTC, as is the case now, is that in the case that the developer wants datetimes expressed to the user in the same time zone as they were entered by the user (which is quite often, I would imagine), they must actually write code to convert the values back to what is already (!) stored in the database before rendering them in templates. I realize that this is only strictly the case when using PostgreSQL, but it still makes very little sense to me.

I don't get why you think it's specific to PostgreSQL. Django provides good cross-database compatibility for something as trivial as datetimes.

The "Time Zone aware output in templates" referred to ​here only works in the most basic invocation of a datetime value (something like {{ object.datetime }}). It doesn't convert the hours if you use something like {{ object.datetime.hour }}, or the day if you use {{ object.datetime.day }}.

Fair enough. Why didn't you bring it up while I was writing the feature? :) Now it's impossible to change :(

I recommend using the |date filter or turning time zone support off.

If only we had a better time zone implementation if Python's stdlib. (In Python 3.5 maybe...)

I find this extremely limited, and quite far removed from Django's usual ability to abstract common tasks.

Thank you.

Obviously it's meant to work as an invisible template tag that you don't have to type in yourself, and I understand that the same template tag wouldn't work for both {{ object.datetime }} and {{ object.datetime.day }}.

I didn't get that part.

But that's why I think it would make more sense for Django to return datetime values as they are formatted in the database by default, rather than automatically converting them to UTC (at least when usage of PostgreSQL has been declared in settings.py).

Like I said above Django *does* return datetime values as stored in the database.

---

To sum up, you misunderstood parts of Django's time zone handling, and I couldn't extract an actionable item from your report. That said, if my comments give you a better picture of Django's behavior, and you have a concrete proposal, please reopen this ticket (or open another one) to suggest changes. Make sure your plan preserves compatibility with existing code.

[Aymeric Augustin]

Replying to anonymous:

If PostgreSQL stores datetimes in UTC internally, but translates them to the connection's time zone as they come back out, doesn't that mean Django is receiving those datetimes in the connection's time zone? Because if so, my point is still that it doesn't make sense for Django to then convert those values back to UTC again.

Django doesn't convert values to UTC. The connection's time zone is set to UTC and Django gets the values in UTC.

​This is why. It says right there in the documentation that while PostgreSQL stores time zone information, other database backends don't. That's why my suggestion was for Django to just leave datetimes in whatever format PostgreSQL gives them, unless the developer explicitly chooses otherwise.

That's what Django does.

Of course, I am basing this on the assumption that Django gets the same data from PostgreSQL as I do when I use psql mydatabase to look at the database directly (for instance, 2013-10-07 01:00:00+08).

This isn't true, unless you run SET TIME ZONE 'UTC' first.

If that's not true and Django *is* directly accessing the UTC-formatted datetimes stored internally by PostgreSQL, then obviously my point is moot.

It is :)