[Django] #28649: Add "week_year" lookup to DateField/DateTimeField
Django
-------------------------------------+-------------------------------------
Reporter: Sigurd | Owner: nobody
Ljødal |
Type: New | Status: assigned
feature |
Component: Database | Version: master
layer (models, ORM) |
Severity: Normal | Keywords: ORM Extract
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
I found myself in the need to extract the ISO year/week-numbering year (as
specified by ISO 8601) from a date/datetime field, e.g. to group values by
week. This is easy with PostgreSQL:
{{{
from django.db.models.functions import Extract, ExtractWeek
SomeModel.objects.annotate(
year=Extract('some_date', 'isoyear'),
week=ExtractWeek('some_date'),
).values(
'year', 'week',
).annotate(
sum=Sum('some_field'),
)
}}}
but unfortunately this does not work across databases. I've implemented an
`ExtractWeekYear` class with support for all databases and will submit a
pull request shortly.
--
Ticket URL: <https://code.djangoproject.com/ticket/28649>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
| Ljødal
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: ORM Extract | Triage Stage:
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* owner: nobody => Sigurd Ljødal
* has_patch: 0 => 1
Comment:
[https://github.com/django/django/pull/9162 PR]
--
Ticket URL: <https://code.djangoproject.com/ticket/28649#comment:1>
Django
-------------------------------------+-------------------------------------
| Ljødal
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by felixxm):
As you mentioned there already exists `ExtractWeek` and `week` lookup (see
#25240) and it works properly on all internal backends. Please clarify
''"this does not work across databases"''. If you're using other database
then you should open a ticket in 3rd party backend not in Django.
--
Ticket URL: <https://code.djangoproject.com/ticket/28649#comment:2>
Django
-------------------------------------+-------------------------------------
| Ljødal
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: felixxm (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/28649#comment:3>
Django
-------------------------------------+-------------------------------------
| Ljødal
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Old description:
> I found myself in the need to extract the ISO year/week-numbering year
> (as specified by ISO 8601) from a date/datetime field, e.g. to group
> values by week. This is easy with PostgreSQL:
>
> {{{
> from django.db.models.functions import Extract, ExtractWeek
>
> SomeModel.objects.annotate(
> year=Extract('some_date', 'isoyear'),
> week=ExtractWeek('some_date'),
> ).values(
> 'year', 'week',
> ).annotate(
> sum=Sum('some_field'),
> )
> }}}
>
> but unfortunately this does not work across databases. I've implemented
> an `ExtractWeekYear` class with support for all databases and will submit
> a pull request shortly.
New description:
I found myself in the need to extract the ISO year/week-numbering year (as
specified by ISO 8601) from a date/datetime field, e.g. to group values by
week. This is easy with PostgreSQL:
{{{
from django.db.models.functions import Extract, ExtractWeek
SomeModel.objects.annotate(
year=Extract('some_date', 'isoyear'),
week=ExtractWeek('some_date'),
).values(
'year', 'week',
).annotate(
sum=Sum('some_field'),
)
}}}
but unfortunately extracting the week year like this does not work across
databases. I've implemented an `ExtractWeekYear` class with support for
all databases and will submit a pull request shortly.
--
Comment (by Sigurd Ljødal):
Hey felixxm, looks like you've misunderstood what I have implemented, it's
not the `ExtractWeek` class, but a cross database solution for
`Extract('some_date', 'isoyear')`. I've updated the description a bit to
clarify this.
The week-numbering year is different from the Julian year, as e.g. January
1st, 2017 is in week 52 of 2016.
--
Ticket URL: <https://code.djangoproject.com/ticket/28649#comment:4>
Django
-------------------------------------+-------------------------------------
| Ljødal
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by felixxm):
Current `ExtractWeek` implementation is based on
[https://en.wikipedia.org/wiki/ISO_8601 ISO-8601] (see
[https://docs.djangoproject.com/en/1.11/ref/models/database-
functions/#extract docs]), therefore it should return 52 for January 1st,
2017. If it doesn't then you've found a bug that should be fixed in the
current implementation.
--
Ticket URL: <https://code.djangoproject.com/ticket/28649#comment:5>
Django
-------------------------------------+-------------------------------------
| Ljødal
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by felixxm):
Sorry, I understand your intention now. Names were confusing. I would call
a class `ExtractIsoYear` and a lookup `iso_year`.
--
Ticket URL: <https://code.djangoproject.com/ticket/28649#comment:6>
Django
-------------------------------------+-------------------------------------
| Ljødal
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Sigurd Ljødal):
Sure, I'm 100% open to changing the name. I wasn't sure how Django
normally did this, as e.g. `ExtractWeek` do not refer to ISO in its name,
even though it follows the ISO-standard. It also appears that I have some
bugs to fix, so I'll have to look into that too.
Just to clarify for anyone else, what I'm intending here is to allow
grouping by year and week, which is currently not possible in a good and
supported way. The issue is with the difference in year as shown below. If
I where to group values using `ExtractYear` and `ExtractWeek`, any values
from Jan. 1st 2017 would show up as week 52 of 2017, which is completely
wrong.
{{{
In [1]: from datetime import date
In [2]: d = date(2017,1,1)
In [3]: d.isocalendar()
Out[3]: (2016, 52, 7)
In [4]: d.year
Out[4]: 2017
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/28649#comment:7>
Django
-------------------------------------+-------------------------------------
Reporter: Sigurd Ljødal | Owner: Sigurd
| Ljødal
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* needs_better_patch: 0 => 1
* stage: Unreviewed => Accepted
--
Ticket URL: <https://code.djangoproject.com/ticket/28649#comment:8>
Django
-------------------------------------+-------------------------------------
Reporter: Sigurd Ljødal | Owner: Sigurd
| Ljødal
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* needs_better_patch: 1 => 0
--
Ticket URL: <https://code.djangoproject.com/ticket/28649#comment:9>
Django
-------------------------------------+-------------------------------------
Reporter: Sigurd Ljødal | Owner: Sigurd
| Ljødal
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* needs_better_patch: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/28649#comment:10>
Django
-------------------------------------+-------------------------------------
Reporter: Sigurd Ljødal | Owner: Sigurd
| Ljødal
Component: Database layer | Version: master
(models, ORM) |
Keywords: ORM Extract | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"3e09b37f80ab34cf57e245e1fcdabb3d4ff92a38" 3e09b37f]:
{{{
#!CommitTicketReference repository=""
revision="3e09b37f80ab34cf57e245e1fcdabb3d4ff92a38"
Fixed #28649 -- Added ExtractIsoYear database function and iso_year
lookup.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/28649#comment:11>