Allow the per-database TIME_ZONE option on databases that support timezones.

[Aymeric Augustin]

I have a Django project that connects to a third-party PostgreSQL database (actually, Redshift, but that's the same as PostgreSQL for the purpose of this ticket).

I do not control the scheme of that database. It uses timestamp (timezone naive) values in UTC for datetimes rather than timestamp with time zone (timezone aware).

Django reads naive datetimes in UTC from this database and assumes they're in Europe/Paris (my global TIME_ZONE setting) so they're off by 2 hours in the admin.

I would like to set the TIME_ZONE option for this database connection to UTC in order to read and write naive datetimes in UTC rather than Europe/Paris.

Unfortunately:

When USE_TZ is True and the database supports time zones (e.g. PostgreSQL), it is an error to set this option.

I think it should be allowed.

[Mariusz Felisiak]

I think it is a duplicate of #23524, closed by you :) (see also related #30288).

[Aymeric Augustin]

#23524 is about changing the timezone of the database connection i.e. at the level of the PostgreSQL server for timestamp with time zone fields generated by Django.

This ticket is about adding time zone information at the level of Django for timestamp fields generated by third-party software — Django doesn't generate such fields when USE_TZ = True.

[Carlton Gibson]

Hey Aymeric,

Early in the morning, before second coffee, but, I can't quite see the (rough) change you're suggesting. Roughly, what does it look like?
(Thanks)

[Carlton Gibson]

Hey Aymeric. I'm going to close this as needsinfo so it's not just stuck on the Unreviewed queue. Please re-open when you have time to expand and we'll have a look. If you prefer to go via the DevelopersMailingList, that's fine too. Thanks! :)

[Aymeric Augustin]

Let me try to explain again.

I'm reading data in a third-party PostgreSQL database where timestamp columns contains values in UTC. I'm using non-managed models and the admin to give users read-only access to this database. As far as I know, all these things are legitimate things to do with Django.

Since my global TIME_ZONE setting is Europe/Paris (CET/UTC+1 or CEST/UTC+2), if a row in this database contains a value that is 9am (UTC), then the admin should show 11am (CEST). However, the admin shows 9am.

Since the admin doesn't show the correct values, users are confused. Since Django doesn't give me a way to configure this, I'm frustrated.

---

I think that the following configuration would adequately reflect the situation:

USE_TZ = True

DATABASES = {
    ...,
    'other': {
        ...,
        'TIME_ZONE': 'UTC',
    },
}

Unfortunately, if I do this, Django throws an error, because of this check which I wrote in ed83881e648:

    def check_settings(self):
        if self.settings_dict['TIME_ZONE'] is not None:
            ...
            elif self.features.supports_timezones:
                raise ImproperlyConfigured(
                    "Connection '%s' cannot set TIME_ZONE because its engine "
                    "handles time zones conversions natively." % self.alias)

I'm proposing to change Django so it will accept this configuration and get the right values from the database and display them correctly in the admin.

Essentially this requires two changes in the PostgreSQL backend:

• adding a database converter for datetimes
• updating the database adapter for datetimes

and, of course, removing the code that prevents this configuration.

[Mariusz Felisiak]

I think it can be more tricky then it looks like to support connection with a native timezone support and in the same time returns datetimes in other timezone. We should avoid unnecessary conversions. It can also be confusing for users.

Tentatively accepted, we can evaluate a patch. I understand that you're going to submit it.

[Aymeric Augustin]

I submitted a PR that does three things:

1. Remove the check that prevented setting the TIME_ZONE option on PostgreSQL
2. Set the database connection's time zone to the value of the TIME_ZONE option
3. Attach the correct time zone information to objects returned from the database

This fixes #23524 and #30288 but not the problem I was having! ;-)

Anyway, I think it's a worthwhile improvement. At least two people want for this feature.

Time zone support has gotten a lot of use since I wrote it. I'm more confident and willing to make this change now than five years ago.

---

My actual issue is that I'm getting naive datetimes from the database (because the fields are defined as timestamp rather than timestamp with time zone) and nothing makes them aware, so they're just displayed "as is" in the admin.

The correct way to tackle this is to write a NaiveDateTimeField to make the appropriate conversions i.e. add time zone information to values read from the database. This doesn't require changes in Django.

---

I'm proposing to close this as wontfix, to reopen #23524, and to change PR 11582 to fix #23524 instead.

[Mariusz Felisiak]

Agreed, thanks!