Skip to content

Commit addd3df

Browse files
kmtraceykmtracey
committed
Fixed #7672 -- Added a 'week_day' lookup type. Many thanks to Ross Poulton for the proposal and implementation on all built-in database backends..
git-svn-id: http://code.djangoproject.com/svn/django/trunk@9818 bcc190cf-cafb-0310-a4f2-bffc1f526a37
1 parent 0326574 commit addd3df

File tree

10 files changed

+68
-15
lines changed

10 files changed

+68
-15
lines changed

‎AUTHORS

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -318,9 +318,10 @@ answer newbie questions, and generally made Django that much better:
318318
Michael Placentra II <[email protected]>
319319
Luke Plant <http://lukeplant.me.uk/>
320320
plisk
321-
Mihai Preda <[email protected]>
322321
Daniel Poelzleithner <http://poelzi.org/>
323322
[email protected]
323+
Ross Poulton <[email protected]>
324+
Mihai Preda <[email protected]>
324325
Matthias Pronk <[email protected]>
325326
Jyrki Pulliainen <[email protected]>
326327
Thejaswi Puthraya <[email protected]>

‎django/db/backends/mysql/base.py

Lines changed: 6 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -116,7 +116,12 @@ class DatabaseFeatures(BaseDatabaseFeatures):
116116
class DatabaseOperations(BaseDatabaseOperations):
117117
def date_extract_sql(self, lookup_type, field_name):
118118
# http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html
119-
return "EXTRACT(%s FROM %s)" % (lookup_type.upper(), field_name)
119+
if lookup_type == 'week_day':
120+
# DAYOFWEEK() returns an integer, 1-7, Sunday=1.
121+
# Note: WEEKDAY() returns 0-6, Monday=0.
122+
return "DAYOFWEEK(%s)" % field_name
123+
else:
124+
return "EXTRACT(%s FROM %s)" % (lookup_type.upper(), field_name)
120125

121126
def date_trunc_sql(self, lookup_type, field_name):
122127
fields = ['year', 'month', 'day', 'hour', 'minute', 'second']

‎django/db/backends/oracle/base.py

Lines changed: 9 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -72,7 +72,11 @@ def autoinc_sql(self, table, column):
7272

7373
def date_extract_sql(self, lookup_type, field_name):
7474
# http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96540/functions42a.htm#1017163
75-
return "EXTRACT(%s FROM %s)" % (lookup_type, field_name)
75+
if lookup_type == 'week_day':
76+
# TO_CHAR(field, 'D') returns an integer from 1-7, where 1=Sunday.
77+
return "TO_CHAR(%s, 'D')" % field_name
78+
else:
79+
return "EXTRACT(%s FROM %s)" % (lookup_type, field_name)
7680

7781
def date_trunc_sql(self, lookup_type, field_name):
7882
# Oracle uses TRUNC() for both dates and numbers.
@@ -268,9 +272,11 @@ def _cursor(self, settings):
268272
self.connection = Database.connect(conn_string, **self.options)
269273
cursor = FormatStylePlaceholderCursor(self.connection)
270274
# Set oracle date to ansi date format. This only needs to execute
271-
# once when we create a new connection.
275+
# once when we create a new connection. We also set the Territory
276+
# to 'AMERICA' which forces Sunday to evaluate to a '1' in TO_CHAR().
272277
cursor.execute("ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS' "
273-
"NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF'")
278+
"NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF' "
279+
"NLS_TERRITORY = 'AMERICA'")
274280
try:
275281
self.oracle_version = int(self.connection.version.split('.')[0])
276282
# There's no way for the DatabaseOperations class to know the

‎django/db/backends/postgresql/operations.py

Lines changed: 6 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -26,7 +26,12 @@ def _get_postgres_version(self):
2626

2727
def date_extract_sql(self, lookup_type, field_name):
2828
# http://www.postgresql.org/docs/8.0/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT
29-
return "EXTRACT('%s' FROM %s)" % (lookup_type, field_name)
29+
if lookup_type == 'week_day':
30+
# Using EXTRACT(), PostgreSQL days are indexed as Sunday=0, Saturday=6.
31+
# If we instead us TO_CHAR, they're indexed with Sunday=1, Saturday=7
32+
return "TO_CHAR(%s, 'D')" % field_name
33+
else:
34+
return "EXTRACT('%s' FROM %s)" % (lookup_type, field_name)
3035

3136
def date_trunc_sql(self, lookup_type, field_name):
3237
# http://www.postgresql.org/docs/8.0/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC

‎django/db/backends/sqlite3/base.py

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -207,7 +207,10 @@ def _sqlite_extract(lookup_type, dt):
207207
dt = util.typecast_timestamp(dt)
208208
except (ValueError, TypeError):
209209
return None
210-
return unicode(getattr(dt, lookup_type))
210+
if lookup_type == 'week_day':
211+
return unicode((dt.isoweekday() % 7) + 1)
212+
else:
213+
return unicode(getattr(dt, lookup_type))
211214

212215
def _sqlite_date_trunc(lookup_type, dt):
213216
try:

‎django/db/models/fields/__init__.py

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -201,7 +201,7 @@ def get_db_prep_lookup(self, lookup_type, value):
201201
sql, params = value.as_sql()
202202
return QueryWrapper(('(%s)' % sql), params)
203203

204-
if lookup_type in ('regex', 'iregex', 'month', 'day', 'search'):
204+
if lookup_type in ('regex', 'iregex', 'month', 'day', 'week_day', 'search'):
205205
return [value]
206206
elif lookup_type in ('exact', 'gt', 'gte', 'lt', 'lte'):
207207
return [self.get_db_prep_value(value)]
@@ -490,9 +490,9 @@ def contribute_to_class(self, cls, name):
490490
curry(cls._get_next_or_previous_by_FIELD, field=self, is_next=False))
491491

492492
def get_db_prep_lookup(self, lookup_type, value):
493-
# For "__month" and "__day" lookups, convert the value to a string so
494-
# the database backend always sees a consistent type.
495-
if lookup_type in ('month', 'day'):
493+
# For "__month", "__day", and "__week_day" lookups, convert the value
494+
# to a string so the database backend always sees a consistent type.
495+
if lookup_type in ('month', 'day', 'week_day'):
496496
return [force_unicode(value)]
497497
return super(DateField, self).get_db_prep_lookup(lookup_type, value)
498498

‎django/db/models/sql/constants.py

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -4,7 +4,7 @@
44
QUERY_TERMS = dict([(x, None) for x in (
55
'exact', 'iexact', 'contains', 'icontains', 'gt', 'gte', 'lt', 'lte', 'in',
66
'startswith', 'istartswith', 'endswith', 'iendswith', 'range', 'year',
7-
'month', 'day', 'isnull', 'search', 'regex', 'iregex',
7+
'month', 'day', 'week_day', 'isnull', 'search', 'regex', 'iregex',
88
)])
99

1010
# Size of each "chunk" for get_iterator calls.

‎django/db/models/sql/where.py

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -174,9 +174,9 @@ def make_atom(self, child, qn):
174174
params)
175175
elif lookup_type in ('range', 'year'):
176176
return ('%s BETWEEN %%s and %%s' % field_sql, params)
177-
elif lookup_type in ('month', 'day'):
178-
return ('%s = %%s' % connection.ops.date_extract_sql(lookup_type,
179-
field_sql), params)
177+
elif lookup_type in ('month', 'day', 'week_day'):
178+
return ('%s = %%s' % connection.ops.date_extract_sql(lookup_type, field_sql),
179+
params)
180180
elif lookup_type == 'isnull':
181181
return ('%s IS %sNULL' % (field_sql,
182182
(not value_annot and 'NOT ' or '')), ())

‎docs/ref/models/querysets.txt

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1344,6 +1344,27 @@ SQL equivalent::
13441344
Note this will match any record with a pub_date on the third day of the month,
13451345
such as January 3, July 3, etc.
13461346

1347+
week_day
1348+
~~~~~~~~
1349+
1350+
.. versionadded:: 1.1
1351+
1352+
For date/datetime fields, a 'day of the week' match.
1353+
1354+
Example::
1355+
1356+
Entry.objects.filter(pub_date__week_day=2)
1357+
1358+
SQL equivalent::
1359+
1360+
SELECT ... WHERE EXTRACT('dow' FROM pub_date) = '2';
1361+
1362+
(The exact SQL syntax varies for each database engine.)
1363+
1364+
Note this will match any record with a pub_date that falls on a Monday (day 2
1365+
of the week), regardless of the month or year in which it occurs. Week days
1366+
are indexed with day 1 being Sunday and day 7 being Saturday.
1367+
13471368
isnull
13481369
~~~~~~
13491370

‎tests/modeltests/basic/models.py

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -74,6 +74,8 @@ def __unicode__(self):
7474
<Article: Area woman programs in Python>
7575
>>> Article.objects.get(pub_date__year=2005, pub_date__month=7, pub_date__day=28)
7676
<Article: Area woman programs in Python>
77+
>>> Article.objects.get(pub_date__week_day=5)
78+
<Article: Area woman programs in Python>
7779
7880
# The "__exact" lookup type can be omitted, as a shortcut.
7981
>>> Article.objects.get(id=1)
@@ -88,6 +90,11 @@ def __unicode__(self):
8890
>>> Article.objects.filter(pub_date__year=2005, pub_date__month=7)
8991
[<Article: Area woman programs in Python>]
9092
93+
>>> Article.objects.filter(pub_date__week_day=5)
94+
[<Article: Area woman programs in Python>]
95+
>>> Article.objects.filter(pub_date__week_day=6)
96+
[]
97+
9198
# Django raises an Article.DoesNotExist exception for get() if the parameters
9299
# don't match any object.
93100
>>> Article.objects.get(id__exact=2)
@@ -100,6 +107,11 @@ def __unicode__(self):
100107
...
101108
DoesNotExist: Article matching query does not exist.
102109
110+
>>> Article.objects.get(pub_date__week_day=6)
111+
Traceback (most recent call last):
112+
...
113+
DoesNotExist: Article matching query does not exist.
114+
103115
# Lookup by a primary key is the most common case, so Django provides a
104116
# shortcut for primary-key exact lookups.
105117
# The following is identical to articles.get(id=1).

0 commit comments

Comments
 (0)