[Django] #29884: Queryset.filter with TruncBase functions not working as expected when USE

Django

unread,

Oct 24, 2018, 7:33:06 AM10/24/18

to django-...@googlegroups.com

#29884: Queryset.filter with TruncBase functions not working as expected when
USE_TZ= True
-------------------------------------+-------------------------------------
Reporter: | Owner: nobody
slide333333 |
Type: | Status: new
Uncategorized |
Component: Database | Version: 2.1
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
** Tested PostgreSQL only **
** USE_TZ=True **

Consider the following model:

{{{
class TimeStampModel(models.Model):
timestamp = models.DateTimeField()
}}}

Create some data:

{{{

TimeStampModel.objects.bulk_create([
TimeStampModel(timestamp=datetime.datetime(2018, 10, 24, 5, 13,
tzinfo=pytz.utc)),
TimeStampModel(timestamp=datetime.datetime(2018, 10, 24, 7, 4,
tzinfo=pytz.utc)),
TimeStampModel(timestamp=datetime.datetime(2018, 10, 24, 8, 56,
tzinfo=pytz.utc)),
TimeStampModel(timestamp=datetime.datetime(2018, 10, 24, 13, 49,
tzinfo=pytz.utc)),
TimeStampModel(timestamp=datetime.datetime(2018, 10, 24, 15, 33,
tzinfo=pytz.utc)),
TimeStampModel(timestamp=datetime.datetime(2018, 10, 24, 18, 29,
tzinfo=pytz.utc)),
TimeStampModel(timestamp=datetime.datetime(2018, 10, 24, 19, 12,
tzinfo=pytz.utc)),
TimeStampModel(timestamp=datetime.datetime(2018, 10, 24, 21, 37,
tzinfo=pytz.utc)),
TimeStampModel(timestamp=datetime.datetime(2018, 10, 24, 21, 9,
tzinfo=pytz.utc)),
TimeStampModel(timestamp=datetime.datetime(2018, 10, 24, 23, 23,
tzinfo=pytz.utc)),
])

}}}

The following code shoud definitely return the data. But it returns an
empty queryset, because the SQL generated is not correct.

{{{
>>> from django.db.models.functions import *
>>> from django.utils import timezone
>>> import datetime, pytz
>>> TimeStampModel.objects.annotate(
... day=TruncDay('timestamp',
tzinfo=pytz.timezone('Europe/Berlin'))).filter(
... day=timezone.make_aware(datetime.datetime(2018, 10, 24),
pytz.timezone('Europe/Berlin'))
... )
DEBUG: (0.000) SELECT "truncbase_timestampmodel"."id",
"truncbase_timestampmodel"."timestamp", DATE_TRUNC('day',
"truncbase_timestampmodel"."timestamp" AT TIME ZONE 'Europe/Berlin') AS
"day" FROM "truncbase_timestampmodel" WHERE DATE_TRUNC('day',
"truncbase_timestampmodel"."timestamp" AT TIME ZONE 'Europe/Berlin') =
'2018-10-23T22:00:00+02:00'::timestamptz LIMIT 21;
args=('datetime.datetime(2018, 10, 23, 22, 0, tzinfo=<DstTzInfo
'Europe/Berlin' CEST+2:00:00 DST>)')
<QuerySet []>
}}}

The SQL should be (note the additional `AT TIME ZONE 'Europe/Berlin'`).

{{{
SELECT "truncbase_timestampmodel"."id",
"truncbase_timestampmodel"."timestamp",
DATE_TRUNC('day', "truncbase_timestampmodel"."timestamp" AT TIME
ZONE 'Europe/Berlin') AT TIME ZONE 'Europe/Berlin' AS "day"
FROM "truncbase_timestampmodel"
WHERE DATE_TRUNC('day', "truncbase_timestampmodel"."timestamp" AT TIME
ZONE 'Europe/Berlin') AT TIME ZONE 'Europe/Berlin' =
'2018-10-23T22:00:00+02:00'::timestamptz
LIMIT 21;
}}}

https://www.postgresql.org/docs/9.2/static/functions-datetime.html
#FUNCTIONS-DATETIME-ZONECONVERT
This fix will also make sure that the returned value from the database
driver is an aware dateime. Currently the returned value is native and
manually made aware in
`django.db.models.functions.datetime.TruncBase.convert_value`!

I'm not sure how the problem relates to databases without timezone
support. But for those with time zone support like PostgreSQL this should
work as expected. For Postgres the fix should be pretty easy:
`django.db.backends.postgresql.operations.DatabaseOperations.datetime_trunc_sql`
has to be patched and
`django.db.models.functions.datetime.TruncBase.convert_value` should be
patched. The latter will also affect other database engines.

--
Ticket URL: <https://code.djangoproject.com/ticket/29884>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

Django

unread,

Oct 24, 2018, 7:39:46 AM10/24/18

to django-...@googlegroups.com

#29884: Queryset.filter with TruncBase functions not working as expected when
USE_TZ= True
-------------------------------------+-------------------------------------

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Description changed by slide333333:

Old description:

New description:

Consider the following model:

Create some data:

{{{

}}}

'2018-10-24T00:00:00+02:00'::timestamptz LIMIT 21;
args=('datetime.datetime(2018, 10, 24, 0, 0, tzinfo=<DstTzInfo

--

--
Ticket URL: <https://code.djangoproject.com/ticket/29884#comment:1>

Django

unread,

Oct 24, 2018, 7:40:32 AM10/24/18

to django-...@googlegroups.com

#29884: Queryset.filter with TruncBase functions not working as expected when
USE_TZ= True
-------------------------------------+-------------------------------------

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Description changed by slide333333:

Old description:

> ** Tested PostgreSQL only **

> '2018-10-24T00:00:00+02:00'::timestamptz LIMIT 21;
> args=('datetime.datetime(2018, 10, 24, 0, 0, tzinfo=<DstTzInfo

New description:

Consider the following model:

Create some data:

{{{

}}}

'2018-10-24T00:00:00+02:00'::timestamptz LIMIT 21;
args=('datetime.datetime(2018, 10, 24, 0, 0, tzinfo=<DstTzInfo

'Europe/Berlin' CEST+2:00:00 DST>)')
<QuerySet []>
}}}

The SQL should be (note the additional `AT TIME ZONE 'Europe/Berlin'`).

{{{
SELECT "truncbase_timestampmodel"."id",
"truncbase_timestampmodel"."timestamp",
DATE_TRUNC('day', "truncbase_timestampmodel"."timestamp" AT TIME
ZONE 'Europe/Berlin') AT TIME ZONE 'Europe/Berlin' AS "day"
FROM "truncbase_timestampmodel"
WHERE DATE_TRUNC('day', "truncbase_timestampmodel"."timestamp" AT TIME
ZONE 'Europe/Berlin') AT TIME ZONE 'Europe/Berlin' =

'2018-10-24T00:00:00+02:00'::timestamptz
LIMIT 21;
}}}

https://www.postgresql.org/docs/9.2/static/functions-datetime.html
#FUNCTIONS-DATETIME-ZONECONVERT
This fix will also make sure that the returned value from the database
driver is an aware dateime. Currently the returned value is native and
manually made aware in
`django.db.models.functions.datetime.TruncBase.convert_value`!

I'm not sure how the problem relates to databases without timezone
support. But for those with time zone support like PostgreSQL this should
work as expected. For Postgres the fix should be pretty easy:
`django.db.backends.postgresql.operations.DatabaseOperations.datetime_trunc_sql`
has to be patched and
`django.db.models.functions.datetime.TruncBase.convert_value` should be
patched. The latter will also affect other database engines.

--

--
Ticket URL: <https://code.djangoproject.com/ticket/29884#comment:2>

Django

unread,

Oct 24, 2018, 7:41:07 AM10/24/18

to django-...@googlegroups.com

#29884: Queryset.filter with TruncBase functions not working as expected when
USE_TZ= True
-------------------------------------+-------------------------------------

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Description changed by slide333333:

Old description:

> ** Tested PostgreSQL only **

> '2018-10-24T00:00:00+02:00'::timestamptz LIMIT 21;
> args=('datetime.datetime(2018, 10, 24, 0, 0, tzinfo=<DstTzInfo

> 'Europe/Berlin' CEST+2:00:00 DST>)')
> <QuerySet []>
> }}}
>
> The SQL should be (note the additional `AT TIME ZONE 'Europe/Berlin'`).
>
> {{{
> SELECT "truncbase_timestampmodel"."id",
> "truncbase_timestampmodel"."timestamp",
> DATE_TRUNC('day', "truncbase_timestampmodel"."timestamp" AT TIME
> ZONE 'Europe/Berlin') AT TIME ZONE 'Europe/Berlin' AS "day"
> FROM "truncbase_timestampmodel"
> WHERE DATE_TRUNC('day', "truncbase_timestampmodel"."timestamp" AT TIME
> ZONE 'Europe/Berlin') AT TIME ZONE 'Europe/Berlin' =

> '2018-10-24T00:00:00+02:00'::timestamptz

> LIMIT 21;
> }}}
>
> https://www.postgresql.org/docs/9.2/static/functions-datetime.html
> #FUNCTIONS-DATETIME-ZONECONVERT
> This fix will also make sure that the returned value from the database
> driver is an aware dateime. Currently the returned value is native and
> manually made aware in
> `django.db.models.functions.datetime.TruncBase.convert_value`!
>
> I'm not sure how the problem relates to databases without timezone
> support. But for those with time zone support like PostgreSQL this should
> work as expected. For Postgres the fix should be pretty easy:
> `django.db.backends.postgresql.operations.DatabaseOperations.datetime_trunc_sql`
> has to be patched and
> `django.db.models.functions.datetime.TruncBase.convert_value` should be
> patched. The latter will also affect other database engines.

New description:

Consider the following model:

Create some data:

{{{

}}}

'2018-10-24T00:00:00+02:00'::timestamptz LIMIT 21;
args=('datetime.datetime(2018, 10, 24, 0, 0, tzinfo=<DstTzInfo

'Europe/Berlin' CEST+2:00:00 DST>)')
<QuerySet []>
}}}

The SQL should be (note the additional `AT TIME ZONE 'Europe/Berlin'`):

{{{
SELECT "truncbase_timestampmodel"."id",
"truncbase_timestampmodel"."timestamp",
DATE_TRUNC('day', "truncbase_timestampmodel"."timestamp" AT TIME
ZONE 'Europe/Berlin') AT TIME ZONE 'Europe/Berlin' AS "day"
FROM "truncbase_timestampmodel"
WHERE DATE_TRUNC('day', "truncbase_timestampmodel"."timestamp" AT TIME
ZONE 'Europe/Berlin') AT TIME ZONE 'Europe/Berlin' =

'2018-10-24T00:00:00+02:00'::timestamptz
LIMIT 21;
}}}

https://www.postgresql.org/docs/9.2/static/functions-datetime.html
#FUNCTIONS-DATETIME-ZONECONVERT
This fix will also make sure that the returned value from the database
driver is an aware dateime. Currently the returned value is native and
manually made aware in
`django.db.models.functions.datetime.TruncBase.convert_value`!

I'm not sure how the problem relates to databases without timezone
support. But for those with time zone support like PostgreSQL this should
work as expected. For Postgres the fix should be pretty easy:
`django.db.backends.postgresql.operations.DatabaseOperations.datetime_trunc_sql`
has to be patched and
`django.db.models.functions.datetime.TruncBase.convert_value` should be
patched. The latter will also affect other database engines.

--

--
Ticket URL: <https://code.djangoproject.com/ticket/29884#comment:3>

Django

unread,

Oct 28, 2018, 11:15:12 PM10/28/18

to django-...@googlegroups.com

#29884: Queryset.filter with TruncBase functions not working as expected when
USE_TZ= True
-------------------------------------+-------------------------------------

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Dan Davis):

I've started the work to triage, and find out which backends may be
affected. Work is in https://github.com/danizen/django-review-29984.
Will test sqlite3, postgresql, mysql, and oracle for good measure.

--
Ticket URL: <https://code.djangoproject.com/ticket/29884#comment:4>

Django

unread,

Nov 1, 2018, 10:10:06 AM11/1/18

to django-...@googlegroups.com

#29884: QuerySet.filter() with TruncBase functions not working as expected when
USE_TZ= True
-------------------------------------+-------------------------------------
Reporter: slide333333 | Owner: nobody
Type: Bug | Status: new

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Tim Graham):

* type: Uncategorized => Bug

Comment:

Can you provide tests for
`tests/db_functions/datetime/test_extract_trunc.py`? It would be easier to
evaluate the ticket with a patch.

--
Ticket URL: <https://code.djangoproject.com/ticket/29884#comment:5>

Django

unread,

Nov 2, 2018, 9:36:03 PM11/2/18

to django-...@googlegroups.com

#29884: QuerySet.filter() with TruncBase functions not working as expected when
USE_TZ= True
-------------------------------------+-------------------------------------

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Dan Davis):

Replying to [comment:5 Tim Graham]:

> Can you provide tests for
`tests/db_functions/datetime/test_extract_trunc.py`? It would be easier to

evaluate the ticket with a patch. tim

I can, but I am wondering about the use of timezone within the database.
I know that Django uses Oracle's DATE type, which is not timezone aware.
The same has got to be true for sqlite3. Really, the timezone in use for
Oracle DATE types is the timezone for the process running Oracle. Also,
evaluating the submission, it is clear that TruncDay still returns a
datetime.datetime, which I would expect.

I will go directly to that once I understand which types Django uses for
each of these. I'm on the track to do this sometime tonight.

--
Ticket URL: <https://code.djangoproject.com/ticket/29884#comment:6>

Django

unread,

Nov 3, 2018, 1:02:49 AM11/3/18

to django-...@googlegroups.com

#29884: QuerySet.filter() with TruncBase functions not working as expected when
USE_TZ= True
-------------------------------------+-------------------------------------
Reporter: slide333333 | Owner: Dan Davis
Type: Bug | Status: assigned

Component: Database layer | Version: 2.1
(models, ORM) |
Severity: Normal | Resolution:

Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 1 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Dan Davis):

* owner: nobody => Dan Davis
* status: new => assigned
* needs_tests: 0 => 1
* stage: Unreviewed => Accepted

Comment:

Only for PostgreSQL does Django use a timezone aware datetime:

Oracle - data type DATE is not timezone aware.
SQLite 3 - data type DATETIME is advisory and not a datetime at all, but
is not timezone aware
MySQL - data type DATETIME(6), stored in UTC but not timezone aware
PostgreSQL - data type TIMESTAMP with TIMEZONE

query generated by
TimeStampModel.objects.annotate(day=TruncDay('timestamp',
tzinfo=pytz.timezone('Europe/Berlin'))) is:

MySQL
{{{
SELECT `app_timestampmodel`.`id`, `app_timestampmodel`.`timestamp`,
CAST(DATE_FORMAT(CONVERT_TZ(`app_timestampmodel`.`timestamp`, 'UTC',
'Europe/Berlin'), '%Y-%m-%d 00:00:00') AS DATETIME) AS `day` FROM
`app_timestampmodel`
}}}

PostgreSQL
{{{
SELECT "app_timestampmodel"."id", "app_timestampmodel"."timestamp",
DATE_TRUNC('day', "app_timestampmodel"."timestamp" AT TIME ZONE
'Europe/Berlin') AS "day" FROM "app_timestampmodel"
}}}l

This makes the intent of Django clear:
* Always return a datetime type
* Do truncation after converting the datetime from database native
(usually UTC) to the given timezone

I've looked at the SQL produced for the filter query as well, and the
DateTrunc doesn't work.

--
Ticket URL: <https://code.djangoproject.com/ticket/29884#comment:7>

Django

unread,

Nov 3, 2018, 6:12:38 PM11/3/18

to django-...@googlegroups.com

#29884: QuerySet.filter() with TruncBase functions not working as expected when
USE_TZ= True
-------------------------------------+-------------------------------------

Has patch: 0 | Needs documentation: 0
Needs tests: 1 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Dan Davis):

Replying to [comment:5 Tim Graham]:
> Can you provide tests for
`tests/db_functions/datetime/test_extract_trunc.py`? It would be easier to
evaluate the ticket with a patch.

After looking at this more deeply, a solution is proposed for PostgreSQL,
and the problem also appears in multiple backends. `TruncBase` delegates
to the backend to do this, calling `connection.ops.datetime_trunc_sql` for
target fields that are datetime fields. I don't see any specific backend
tests for datetime truncation, but I see that
see`test/backends/base/test_operations.py` might be a good place. I do
not see how backend specific tests are invoked, but I see some with skips.

--
Ticket URL: <https://code.djangoproject.com/ticket/29884#comment:8>

Django

unread,

Nov 3, 2018, 6:29:01 PM11/3/18

to django-...@googlegroups.com

#29884: QuerySet.filter() with TruncBase functions not working as expected when
USE_TZ= True
-------------------------------------+-------------------------------------

Has patch: 0 | Needs documentation: 0
Needs tests: 1 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Tim Graham):

I don't expect this would require backend-specific tests. The queries
results should be the same across all databases, shouldn't they? I haven't
looking into this issue to say whether or not there's a bug here.

--
Ticket URL: <https://code.djangoproject.com/ticket/29884#comment:9>

Django

unread,

Nov 6, 2018, 12:17:16 AM11/6/18

to django-...@googlegroups.com

#29884: QuerySet.filter() with TruncBase functions not working as expected when
USE_TZ= True
-------------------------------------+-------------------------------------
Reporter: slide333333 | Owner: (none)
Type: Bug | Status: new

Component: Database layer | Version: 2.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Dan Davis):

* owner: Dan Davis => (none)
* status: assigned => new
* needs_tests: 1 => 0

Comment:

Replying to [comment:9 Tim Graham]:

> I don't expect this would require backend-specific tests. The queries
results should be the same across all databases, shouldn't they? I haven't
looking into this issue to say whether or not there's a bug here.

This would be fixed in each backend separately, because the method to
truncate a datetime is delegated to each backend separately.
Nevertheless, since the unit tests are run against a specific backend, by
default sqlite3, the test can be in
`tests/db_functions/datetime/test_extract_trunc.py`.

Pull request https://github.com/django/django/pull/10611 provides a
failing test for the filtering case. It isn't ready, but anyone else can
see the diff.

I've run this against sqlite3 and also against postgresql like this:
{{{
./runtests.py --settings postgresql_settings
db_functions.datetime.test_extract_trunc
}}}

I'm out of this one - I would want someone with more experience in the
project to decide whether the single ticket should be fixed for all
backends or just for PostgreSQL.

One thing I notice is that make_aware is surprisingly not the same thing
as adding the tzinfo to the datetime in the first place. The following
raises:

{{{
from datetime import datetime
import pytz
from django.utils.timezone import make_aware

euberlin = pytz.timezone('Europe/Berlin')s a
dt1 = datetime(2018, 10, 24, tzinfo=euberlin)
dt2 = make_aware(datetime(2018, 10, 24))
assert dt1 == dt2
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/29884#comment:10>

Django

unread,

Nov 6, 2018, 9:43:11 AM11/6/18

to django-...@googlegroups.com

#29884: QuerySet.filter() with TruncBase functions not working as expected when
USE_TZ= True
-------------------------------------+-------------------------------------

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Simon Charette):

> One thing I notice is that make_aware is surprisingly not the same thing
as adding the tzinfo to the datetime in the first place.

Except for the `TIME_ZONE` setting omission that
[https://groups.google.com/d/msg/django-
developers/cyqlZnN3HUQ/CRhOOft1AwAJ Aymeric mentioned on the mailing list]
there's a big difference between `datetime(2018, 10, 24, tzinfo=tz)` and
`tz.localize(datetime(2018, 10, 24))` which is what `make_aware` does. The
first will assign the timezone `Europe/Berlin` timezone
[https://en.wikipedia.org/wiki/Time_in_Germany#History as it was first
defined] and the latter will assign the timezone as it is defined on the
specified datetime.

In other words direct `tzinfo`, assignment will create highly likely bogus
datetime objects (unless a timezone definition such as UTC is used) while
`localize` will make sure the right timezone is used. In your case
`datetime(2018, 10, 24, tzinfo=euberlin)` creates a datetime for
2018-10-24 with the timezone definition of `Europe/Berlin` in 1901.

--
Ticket URL: <https://code.djangoproject.com/ticket/29884#comment:11>

Django

unread,

Nov 7, 2018, 12:16:15 AM11/7/18

to django-...@googlegroups.com

#29884: QuerySet.filter() with TruncBase functions not working as expected when
USE_TZ= True
-------------------------------------+-------------------------------------

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Dan Davis):

Replying to [comment:11 Simon Charette]:

> Except for the `TIME_ZONE` setting omission that
[https://groups.google.com/d/msg/django-
developers/cyqlZnN3HUQ/CRhOOft1AwAJ Aymeric mentioned on the mailing list]
there's a big difference between `datetime(2018, 10, 24, tzinfo=tz)` and
`tz.localize(datetime(2018, 10, 24))` which is what `make_aware` does. The
first will assign the timezone `Europe/Berlin` timezone
[https://en.wikipedia.org/wiki/Time_in_Germany#History as it was first
defined] and the latter will assign the timezone as it is defined on the
specified datetime.
>
> In other words direct `tzinfo`, assignment will create highly likely
bogus datetime objects (unless a timezone definition such as UTC is used)
while `localize` will make sure the right timezone is used. In your case
`datetime(2018, 10, 24, tzinfo=euberlin)` creates a datetime for
2018-10-24 with the timezone definition of `Europe/Berlin` in 1901.

Thanks for the explanation. Got it.

--
Ticket URL: <https://code.djangoproject.com/ticket/29884#comment:12>

Django

unread,

Mar 12, 2024, 3:19:24 AM3/12/24

to django-...@googlegroups.com

#29884: QuerySet.filter() with TruncBase functions not working as expected when
USE_TZ= True
-------------------------------------+-------------------------------------

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Ülgen Sarıkavak):

* cc: Ülgen Sarıkavak (added)

Old description:

> '2018-10-24T00:00:00+02:00'::timestamptz LIMIT 21;
> args=('datetime.datetime(2018, 10, 24, 0, 0, tzinfo=<DstTzInfo

> 'Europe/Berlin' CEST+2:00:00 DST>)')
> <QuerySet []>
> }}}
>

> The SQL should be (note the additional `AT TIME ZONE 'Europe/Berlin'`):
>
> {{{

> SELECT "truncbase_timestampmodel"."id",
> "truncbase_timestampmodel"."timestamp",
> DATE_TRUNC('day', "truncbase_timestampmodel"."timestamp" AT TIME
> ZONE 'Europe/Berlin') AT TIME ZONE 'Europe/Berlin' AS "day"
> FROM "truncbase_timestampmodel"
> WHERE DATE_TRUNC('day', "truncbase_timestampmodel"."timestamp" AT TIME
> ZONE 'Europe/Berlin') AT TIME ZONE 'Europe/Berlin' =

> '2018-10-24T00:00:00+02:00'::timestamptz

> LIMIT 21;
> }}}
>
> https://www.postgresql.org/docs/9.2/static/functions-datetime.html
> #FUNCTIONS-DATETIME-ZONECONVERT
> This fix will also make sure that the returned value from the database
> driver is an aware dateime. Currently the returned value is native and
> manually made aware in
> `django.db.models.functions.datetime.TruncBase.convert_value`!
>
> I'm not sure how the problem relates to databases without timezone
> support. But for those with time zone support like PostgreSQL this should
> work as expected. For Postgres the fix should be pretty easy:
> `django.db.backends.postgresql.operations.DatabaseOperations.datetime_trunc_sql`
> has to be patched and
> `django.db.models.functions.datetime.TruncBase.convert_value` should be
> patched. The latter will also affect other database engines.

New description:

'2018-10-24T00:00:00+02:00'::timestamptz LIMIT 21;
args=('datetime.datetime(2018, 10, 24, 0, 0, tzinfo=<DstTzInfo

'Europe/Berlin' CEST+2:00:00 DST>)')
<QuerySet []>
}}}

The SQL should be (note the additional `AT TIME ZONE 'Europe/Berlin'`):

{{{

SELECT "truncbase_timestampmodel"."id",
"truncbase_timestampmodel"."timestamp",
DATE_TRUNC('day', "truncbase_timestampmodel"."timestamp" AT TIME
ZONE 'Europe/Berlin') AT TIME ZONE 'Europe/Berlin' AS "day"
FROM "truncbase_timestampmodel"
WHERE DATE_TRUNC('day', "truncbase_timestampmodel"."timestamp" AT TIME
ZONE 'Europe/Berlin') AT TIME ZONE 'Europe/Berlin' =

'2018-10-24T00:00:00+02:00'::timestamptz

LIMIT 21;
}}}

https://www.postgresql.org/docs/9.2/static/functions-datetime.html
#FUNCTIONS-DATETIME-ZONECONVERT
This fix will also make sure that the returned value from the database
driver is an aware dateime. Currently the returned value is native and
manually made aware in
`django.db.models.functions.datetime.TruncBase.convert_value`!

I'm not sure how the problem relates to databases without timezone
support. But for those with time zone support like PostgreSQL this should
work as expected. For Postgres the fix should be pretty easy:
`django.db.backends.postgresql.operations.DatabaseOperations.datetime_trunc_sql`
has to be patched and
`django.db.models.functions.datetime.TruncBase.convert_value` should be
patched. The latter will also affect other database engines.

--

--
Ticket URL: <https://code.djangoproject.com/ticket/29884#comment:13>

Reply all

Reply to author

Forward

[Django] #29884: Queryset.filter with TruncBase functions not working as expected when USE_TZ= True

Django

Django

Django

Django

Django

Django

Django

Django

Django

Django

Django

Django

Django

Django