#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 <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/069.e9fe3d46e11592f9aa4c0c54b77a54ec%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
