Opened 7 weeks ago

Last modified 6 weeks ago

#28649 assigned New feature

Add "iso_year" lookup to DateField/DateTimeField

Reported by: Sigurd Ljødal Owned by: Sigurd Ljødal
Component: Database layer (models, ORM) Version: master
Severity: Normal Keywords: ORM Extract
Cc: felixxm Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: yes
Easy pickings: no UI/UX: no

Description (last modified by 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.

Change History (8)

comment:1 Changed 7 weeks ago by Sigurd Ljødal

Has patch: set
Owner: changed from nobody to Sigurd Ljødal

comment:2 Changed 7 weeks ago 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".

Last edited 7 weeks ago by felixxm (previous) (diff)

comment:3 Changed 7 weeks ago by felixxm

Cc: felixxm added

comment:4 Changed 7 weeks ago by Sigurd Ljødal

Description: modified (diff)

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.

comment:5 Changed 7 weeks ago by felixxm

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.

comment:6 Changed 7 weeks ago by felixxm

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

comment:7 Changed 7 weeks ago 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

comment:8 Changed 6 weeks ago by Tim Graham

Patch needs improvement: set
Summary: Add "week_year" lookup to DateField/DateTimeFieldAdd "iso_year" lookup to DateField/DateTimeField
Triage Stage: UnreviewedAccepted
Note: See TracTickets for help on using tickets.
Back to Top