id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
28933	Implement a range-based filter for Django Admin date_hierarchy	hakib	nobody	"The predicate generated by date_hierarchy makes it very difficult for databases to optimize the query.

The following date hierarchy:

{{{
/admin/app/model?created__year=2017&created__month=12&created__day=16
}}}
 
Will generate the following where clause (PostgreSql):

{{{
WHERE created between '2017-01-01' and '2017-31-12' and EXTRACT('month', created) = 12 and EXTRACT('day', created) = 16
}}}


The query above will not be able to utilize range based indexes on the date hierarchy column - on big tables this has a significant performance impact.

The current implementation of date hierarchy is relying on the ""default"" filtering mechinizem used by Django Admin. **I propose implementing custom filtering for Django Admin that will better utilize it's hierarchical nature and make it more database ""friendly"".**

Instead of the query above the date hierarchy would generate the following predicates for different levels of the heirarchy:


{{{
/admin/app/model?created__year=2017&created__month=12&created__day=16 
WHERE created >= '2017-12-16' and created < '2017-12-17'
}}}


{{{
/admin/app/model?created__year=2017&created__month=12
WHERE created >= '2017-12-01' and created < '2018-01-01'
}}}


{{{
/admin/app/model?created__year=2017
WHERE created >= '2017-01-01' and created < '2018-01-01'
}}}


I already [wrote about this issue](https://codeburst.io/django-admin-range-based-date-hierarchy-37955b12ea4e) and [published a package](https://github.com/hakib/django-admin-lightweight-date-hierarchy/blob/master/django_admin_lightweight_date_hierarchy/admin.py) that implement the above as a custom SimpleListFilter.

I already have a PR ready - I'm following proper protocol here so please let me know if this is acceptable.


"	New feature	new	contrib.admin	2.0	Normal		date_hierarchy		Unreviewed	0	0	0	0	0	0