Add "iso_year" lookup to DateField/DateTimeField

[Sigurd Ljødal]

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.

[Sigurd Ljødal]

​PR

[Mariusz Felisiak]

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.

[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.

[Mariusz Felisiak]

Current ExtractWeek implementation is based on ​ISO-8601 (see ​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.

[Mariusz Felisiak]

Sorry, I understand your intention now. Names were confusing. I would call a class ExtractIsoYear and a lookup iso_year.

[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

[Tim Graham <timograham@…>]

In 3e09b37f:

Fixed #28649 -- Added ExtractIsoYear database function and iso_year lookup.