Now() database function doesn't respect TIME_ZONE with USE_TZ

[lobziik]

Hi guys!
Seems that PostgreSQL database wrapper doesn't set the timezone on connection initialization.

My database settings:

{
  "NAME": null,
  "USER": "lobziik",
  "PASSWORD": "",
  "HOST": "127.0.0.1",
  "PORT": 5432,
  "ENGINE": "django.db.backends.postgresql",
  "ATOMIC_REQUESTS": false,
  "AUTOCOMMIT": true,
  "CONN_MAX_AGE": 0,
  "OPTIONS": {
    
  },
  "TIME_ZONE": null,
  "TEST": {
    "CHARSET": null,
    "COLLATION": null,
    "NAME": "test_django_test",
    "MIRROR": null
  }
}

psql behaviour on this database

$ psql -d test_django_test

psql (10.4)
Type "help" for help.

test_django_test=# select * from statement_timestamp();
              now
-------------------------------
 2018-07-20 01:37:54.903856+02
(1 row)

system date:

$ date
Fri Jul 20 01:38:44 CEST 2018

Trying to annotate some qs with built-in Django Now function:

In [8]: User.objects.all().annotate(now=Now()).last().now
Out[8]: datetime.datetime(2018, 7, 19, 23, 42, 51, 125068, tzinfo=<UTC>)

In [15]: settings.TIME_ZONE
Out[15]: 'Europe/Prague'

In [13]: settings.USE_TZ
Out[13]: True

I found strange behaviour in django.db.backends.base.base.BaseDatabaseWrapper#timezone_name, this prop is used in django.db.backends.postgresql.base.DatabaseWrapper#ensure_timezone for checking, if we should set timezone for connection or not.
But with USE_TZ = True, it always will be UTC in postgres case.

    @cached_property
    def timezone_name(self):
        """
        Name of the time zone of the database connection.
        """
        if not settings.USE_TZ:
            return settings.TIME_ZONE
        elif self.settings_dict['TIME_ZONE'] is None:
            return 'UTC'
        else:
            return self.settings_dict['TIME_ZONE']

Also it impossible to pass TIME_ZONE manually for postgres database. See django.db.backends.base.base.BaseDatabaseWrapper#check_settings

[lobziik]

Also, documentation mentions that connection timezone should be set as TIME_ZONE: ​https://docs.djangoproject.com/en/2.0/topics/i18n/timezones/#postgresql

[Tim Graham]

I don't see anything in ​the documentation for the Now database function that suggests that settings.TIME_ZONE should be respected: "Returns the database server’s current date and time when the query is executed".

From the ​time zone documentation, "When support for time zones is enabled, Django stores datetime information in UTC in the database, uses time-zone-aware datetime objects internally, and translates them to the end user’s time zone in templates and forms."

[lobziik]

Replying to Tim Graham:

I don't see anything in ​the documentation for the Now database function that suggests that settings.TIME_ZONE should be respected: "Returns the database server’s current date and time when the query is executed".

From the ​time zone documentation, "When support for time zones is enabled, Django stores datetime information in UTC in the database, uses time-zone-aware datetime objects internally, and translates them to the end user’s time zone in templates and forms."

Yep, seems title is so misleading. Can I change it and reopen ticket?

The main problem described is:

Seems that PostgreSQL database wrapper doesn't set the timezone on connection initialization.

And Now() behaviour is just a side effect.

And according to ​time zone documentation: "As a consequence, if you’re using PostgreSQL, you can switch between USE_TZ = False and USE_TZ = True freely. The database connection’s time zone will be set to TIME_ZONE or UTC respectively, so that Django obtains correct datetimes in all cases. You don’t need to perform any data conversions."

[Tim Graham]

It looks to me like ​the time zone is set if needed. ​The code matches the documentation you quoted as far as I see.

[lobziik]

Replying to Tim Graham:

It looks to me like ​the time zone is set if needed. ​The code matches the documentation you quoted as far as I see.

timezone_name looks broken for me...
If settings.TIME_ZONE is True, UTC returns ​here
If TIME_ZONE presented in database settings it will cause the ImproperlyConfigured exception ​here.

Should we patch ​this if statement and remove not there? Or redefine this method in postgresql.base.DatabaseWrapper.

[Tim Graham]

Paraphrasing the documentation you quoted, "The database connection’s time zone will be set to TIME_ZONE (if USE_TZ is False) or UTC (if USE_TZ is True)." This statement matches the implementation of BaseDatabaseWrapper.timezone_name as far as I see.

I still don't see a problem.