Opened 16 months ago

Closed 5 months ago

#28649 closed New feature (fixed)

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

    year=Extract('some_date', 'isoyear'),
    'year', 'week',

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 (11)

comment:1 Changed 16 months ago by Sigurd Ljødal

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

comment:2 Changed 16 months 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 16 months ago by felixxm (previous) (diff)

comment:3 Changed 16 months ago by felixxm

Cc: felixxm added

comment:4 Changed 16 months 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 16 months 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 16 months 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 16 months 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 16 months 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

comment:9 Changed 11 months ago by Tim Graham

Patch needs improvement: unset

comment:10 Changed 10 months ago by Tim Graham

Patch needs improvement: set

comment:11 Changed 5 months ago by Tim Graham <timograham@…>

Resolution: fixed
Status: assignedclosed

In 3e09b37f:

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

Note: See TracTickets for help on using tickets.
Back to Top