[Django] #25240: Current week query for DateTime fields
Django
----------------------------------------------+----------------------------
Reporter: foresmac | Owner: nobody
Type: Uncategorized | Status: new
Component: Database layer (models, ORM) | Version: 1.8
Severity: Normal | Keywords: QuerySet.extra
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+----------------------------
Per the recommendation
[https://docs.djangoproject.com/en/1.8/ref/models/querysets/#extra here],
filling a ticket requesting that a "give me all items for the current
week" option be added to the ORM to filter `DateTime` fields. I'm using
the following extra `WHERE` clause to achieve this:
{{{#!python
where=['EXTRACT(WEEK FROM timestamp) = EXTRACT(WEEK FROM
CURRENT_TIMESTAMP)'])
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/25240>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Reporter: foresmac | Owner: nobody
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: QuerySet.extra | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by timgraham):
* needs_better_patch: => 0
* needs_tests: => 0
* version: 1.8 => master
* needs_docs: => 0
* type: Uncategorized => New feature
* stage: Unreviewed => Accepted
--
Ticket URL: <https://code.djangoproject.com/ticket/25240#comment:1>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: QuerySet.extra | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by jarshwah):
I'm fairly sure that this can be implemented in 1.9 with bilateral
transformations. 1.9 is only required because that is when `Now()` was
added. If you'd like to copy the implementation of `Now()` into your
current codebase, then you could get it to work in 1.8.
{{{
from django.db.models.fields import DateTransform
from django.db.models.functions import Now
class WeekTransform(DateTransform):
lookup_name = 'week'
bilateral = True
Model.objects.filter(timestamp__week__exact=Now())
}}}
So there's an argument to be made that `WeekTransform` could be added to
core, but without the `bilateral` argument in order to remain consistent
with the rest of the Date based transforms. I'm currently working on a
patch (#24629) that would allow the transform to be used explicitly on the
right hand side:
{{{
class WeekTransform(DateTransform):
lookup_name = 'week'
Model.objects.filter(timestamp__week__exact=WeekTransform(Now()))
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/25240#comment:2>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: QuerySet.extra | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by tarkatronic):
* cc: tarkatronic@… (added)
Comment:
I just tried creating such a lookup (by subclassing DateLookup) but I ran
into a brick wall in django.db.models.fields. The lookup itself is very
simple, emulating precisely how the 'year', 'day', etc lookups are done:
{{{
#!python
from django.db.models import DateTimeField
from django.db.models.lookup import DateLookup
@DateTimeField.register_lookup
class WeekLookup(DateLookup):
lookup_name = 'week'
extract_type = 'week'
}}}
Simple enough. The problem, however, comes from
django/db/models/fields/!__init!__.py:
{{{
#!python
def get_prep_lookup(self, lookup_type, value):
# For dates lookups, convert the value to an int
# so the database backend always sees a consistent type.
if lookup_type in ('month', 'day', 'week_day', 'hour', 'minute',
'second'):
return int(value)
return super(DateField, self).get_prep_lookup(lookup_type, value)
}}}
There are two other such special cases in that file. Perhaps I'm just
being a bit pedantic, but this seems to violate PEP 20:
Special cases aren't special enough to break the rules.
Shy of subclassing DateTimeField, it would seem that there's no way, at
present, to use this lookup.
Additionally, as a note on jarshwah's suggestion, you would also have to
copy in the implementation of DateTransform, since that isn't present in
1.8 either. A complete implementation of the suggestion looks like:
{{{
#!python
from django.conf import settings
from django.db.models.fields import DateField, DateTimeField,
IntegerField, TimeField
from django.db.models.functions import Func
from django.db.models.lookups import Transform
from django.utils import timezone
from django.utils.functional import cached_property
class Now(Func):
template = 'CURRENT_TIMESTAMP'
def __init__(self, output_field=None, **extra):
if output_field is None:
output_field = DateTimeField()
super(Now, self).__init__(output_field=output_field, **extra)
def as_postgresql(self, compiler, connection):
# Postgres' CURRENT_TIMESTAMP means "the time at the start of the
# transaction". We use STATEMENT_TIMESTAMP to be cross-compatible
with
# other databases.
self.template = 'STATEMENT_TIMESTAMP()'
return self.as_sql(compiler, connection)
class DateTransform(Transform):
def as_sql(self, compiler, connection):
sql, params = compiler.compile(self.lhs)
lhs_output_field = self.lhs.output_field
if isinstance(lhs_output_field, DateTimeField):
tzname = timezone.get_current_timezone_name() if
settings.USE_TZ else None
sql, tz_params =
connection.ops.datetime_extract_sql(self.lookup_name, sql, tzname)
params.extend(tz_params)
elif isinstance(lhs_output_field, DateField):
sql = connection.ops.date_extract_sql(self.lookup_name, sql)
elif isinstance(lhs_output_field, TimeField):
sql = connection.ops.time_extract_sql(self.lookup_name, sql)
else:
raise ValueError('DateTransform only valid on
Date/Time/DateTimeFields')
return sql, params
@cached_property
def output_field(self):
return IntegerField()
@DateTimeField.register_lookup
class WeekTransform(DateTransform):
lookup_name = 'week'
bilateral = True
}}}
This does work with the suggested usage:
{{{
#!python
Model.objects.filter(timestamp__week__exact=Now())
}}}
However, there is one major limitation. When you do the comparison against
a week number, as you would do with the other date part comparisons, you
get an `AttributeError: 'QueryWrapper' object has no attribute
'output_field'`. And when comparing to a full datetime object, you get
`TypeError: int() argument must be a string, a bytes-like object or a
number, not 'datetime.datetime'`.
So long story short, the transform approach does work, but does not stay
consistent with the existing lookups, and adding a "!__week" lookup will
require modification of django/db/models/fields/!__init!__.py
--
Ticket URL: <https://code.djangoproject.com/ticket/25240#comment:3>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: QuerySet.extra | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by borfast):
* cc: borfast@… (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/25240#comment:4>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: QuerySet.extra | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by jarshwah):
* cc: josh.smeaton@… (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/25240#comment:5>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: QuerySet.extra | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
Changes (by Mads Jensen):
* needs_better_patch: 0 => 1
* has_patch: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/25240#comment:6>
Django
-------------------------------------+-------------------------------------
| Jensen
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: QuerySet.extra | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Mads Jensen):
* status: new => assigned
* needs_better_patch: 1 => 0
* owner: nobody => Mads Jensen
--
Ticket URL: <https://code.djangoproject.com/ticket/25240#comment:7>
Django
-------------------------------------+-------------------------------------
Reporter: Chris Foresman | Owner: Mads
| Jensen
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
| checkin
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Rivo Laks):
* cc: code+django@… (added)
* stage: Accepted => Ready for checkin
Comment:
I have no further comments to the PR
(https://github.com/django/django/pull/7447), LGTM.
--
Ticket URL: <https://code.djangoproject.com/ticket/25240#comment:8>
Django
-------------------------------------+-------------------------------------
Reporter: Chris Foresman | Owner: Mads
| Jensen
Component: Database layer | Version: master
(models, ORM) |
Keywords: QuerySet.extra | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Tim Graham <timograham@…>):
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"1446902be48ebf19bfe484371897a2815dd21fca" 1446902b]:
{{{
#!CommitTicketReference repository=""
revision="1446902be48ebf19bfe484371897a2815dd21fca"
Fixed #25240 -- Added ExtractWeek and exposed it through the __week
lookup.
Thanks to Mariusz Felisiak and Tim Graham for review.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/25240#comment:9>
Django
-------------------------------------+-------------------------------------
Reporter: Chris Foresman | Owner: Mads
| Jensen
Type: New feature | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: QuerySet.extra | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Tim Graham <timograham@…>):
In [changeset:"085c2f94ec0155417601a9750ad60bb93536e166" 085c2f94]:
{{{
#!CommitTicketReference repository=""
revision="085c2f94ec0155417601a9750ad60bb93536e166"
Refs #25240 -- Added ExtractWeek examples.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/25240#comment:10>
Django
-------------------------------------+-------------------------------------
Reporter: Chris Foresman | Owner: Mads
| Jensen
Type: New feature | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: QuerySet.extra | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Tim Graham <timograham@…>):
In [changeset:"319839d7805efe48da82d7565b638c3b872461ae" 319839d7]:
{{{
#!CommitTicketReference repository=""
revision="319839d7805efe48da82d7565b638c3b872461ae"
[1.11.x] Refs #25240 -- Added ExtractWeek examples.
Backport of 085c2f94ec0155417601a9750ad60bb93536e166 from master
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/25240#comment:11>