[Django] #29580: Postgresql: db Now() doesnt respect TIME_ZONE with USE_TZ
Django
-------------------------------------+-------------------------------------
Reporter: lobziik | Owner: nobody
Type: Bug | Status: new
Component: Database | Version: 2.0
layer (models, ORM) | Keywords: postgres,
Severity: Normal | postgresql, time zones, USE_TZ
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/29580>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Reporter: lobziik | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: postgres, | Triage Stage:
postgresql, time zones, USE_TZ | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by lobziik):
Also, documentation mentions that connection timezone should be set as
TIME_ZONE:
https://docs.djangoproject.com/en/2.0/topics/i18n/timezones/#postgresql
--
Ticket URL: <https://code.djangoproject.com/ticket/29580#comment:1>
Django
-------------------------------------+-------------------------------------
Reporter: lobziik | Owner: nobody
Component: Database layer | Version: 2.0
(models, ORM) |
Keywords: postgres, | Triage Stage:
postgresql, time zones, USE_TZ | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => invalid
Comment:
I don't see anything in [https://docs.djangoproject.com/en/dev/ref/models
/database-functions/#now 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 [https://docs.djangoproject.com/en/dev/topics/i18n/timezones/
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."
--
Ticket URL: <https://code.djangoproject.com/ticket/29580#comment:2>
Django
-------------------------------------+-------------------------------------
Reporter: lobziik | Owner: nobody
Component: Database layer | Version: 2.0
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: postgres, | Triage Stage:
postgresql, time zones, USE_TZ | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by lobziik):
Replying to [comment:2 Tim Graham]:
> I don't see anything in
[https://docs.djangoproject.com/en/dev/ref/models/database-functions/#now
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 [https://docs.djangoproject.com/en/dev/topics/i18n/timezones/
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
[https://docs.djangoproject.com/en/2.0/topics/i18n/timezones/#postgresql
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."
--
Ticket URL: <https://code.djangoproject.com/ticket/29580#comment:3>
Django
-------------------------------------+-------------------------------------
Reporter: lobziik | Owner: nobody
Component: Database layer | Version: 2.0
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: postgres, | Triage Stage:
postgresql, time zones, USE_TZ | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Tim Graham):
It looks to me like
[https://github.com/django/django/blob/ba83378a7762c51be235b521aa5b48233d6c6c82/django/db/backends/postgresql/base.py#L193-L201
the time zone is set if needed].
[https://github.com/django/django/blob/ba83378a7762c51be235b521aa5b48233d6c6c82/django/db/backends/base/base.py#L135-L145
The code] matches the documentation you quoted as far as I see.
--
Ticket URL: <https://code.djangoproject.com/ticket/29580#comment:4>
Django
-------------------------------------+-------------------------------------
Reporter: lobziik | Owner: nobody
Component: Database layer | Version: 2.0
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: postgres, | Triage Stage:
postgresql, time zones, USE_TZ | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by lobziik):
Replying to [comment:4 Tim Graham]:
> It looks to me like
[https://github.com/django/django/blob/ba83378a7762c51be235b521aa5b48233d6c6c82/django/db/backends/postgresql/base.py#L193-L201
the time zone is set if needed].
[https://github.com/django/django/blob/ba83378a7762c51be235b521aa5b48233d6c6c82/django/db/backends/base/base.py#L135-L145
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
[https://github.com/django/django/blob/ba83378a7762c51be235b521aa5b48233d6c6c82/django/db/backends/base/base.py#L143
here]
If TIME_ZONE presented in database settings it will cause the
ImproperlyConfigured exception
[https://github.com/django/django/blob/ba83378a7762c51be235b521aa5b48233d6c6c82/django/db/backends/base/base.py#L207
here].
Should we patch
[https://github.com/django/django/blob/ba83378a7762c51be235b521aa5b48233d6c6c82/django/db/backends/base/base.py#L140
this] if statement and remove not there? Or redefine this method in
{{{postgresql.base.DatabaseWrapper}}}.
--
Ticket URL: <https://code.djangoproject.com/ticket/29580#comment:5>
Django
-------------------------------------+-------------------------------------
Reporter: lobziik | Owner: nobody
Component: Database layer | Version: 2.0
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: postgres, | Triage Stage:
postgresql, time zones, USE_TZ | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by 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.
--
Ticket URL: <https://code.djangoproject.com/ticket/29580#comment:6>