How can I filter a DateField or a DateTimeField in Django using a string?

Question

I am using DataTables for my front end and it allows a global search (iterating through all the fields to find matches).

This is what I want to be able to do in django (version 1.9.1):

use “ma” as a filter for a DateField so that all March and May dates are returned

or

use “Mar 1” as a filter for a DateField so that all dates between March 10 – March 19 are returned

After some tinkering, I have this custom lookup

from django.db.models import Lookup
from django.db.models.fields import DateField

@DateField.register_lookup
class DateTextFilter(Lookup):
    lookup_name = 'dttxt'
    def as_postgresql(self, compiler, connection):
        lhs, lhs_params = self.process_lhs(compiler, connection)
        rhs = self.rhs.strftime("%b %d, %Y")
        return "to_char(%s,'Mon DD, YYYY') ~* '%s'", ([lhs,rhs])

which I run like this:

outputQ = Q(**{"appt_date"+"__dttxt" : "Mar 09"})
Appts.objects.filter(outputQ)

It produces SQL that runs (in Postgres) but when I run filter(), I get the error

ValidationError: [u"'Mar 09' value has an invalid date format. 
It must be in YYYY-MM-DD format."]

Fair enough, “Mar 09” is not a valid date – but the raw SQL is valid and runs ( I tested this with '2016-03-09' to bypass this error and get django to create the SQL)

SELECT "appts"."appt_id", "appts"."client_id", "appts"."therapist_id",
 "appts"."agency_id", "appts"."appt_date", "appts"."appt_start", 
"appts"."appt_end", "appts"."cpt_code", "appts"."client_owe", 
"appts"."insur1_owe", "appts"."insur2_owe", "appts"."note", 
"appts"."note_flag", "appts"."ct_fee_pd", "appts"."subm_date", 
"appts"."updated" FROM "appts" WHERE to_char("appts"."appt_date",'Mon DD, 
YYYY') ~* 'Mar 09'

I could use extra() but this will be deprecated.

I am probably going to wind up parsing the string using this SO algorithm (using raw sql is too large of a compromise just to gain this functionality), but I will lose some of the functionality that I want .

Not sure if this is doable...

MIkee · Accepted Answer · 2017-12-17T23:36:14.923

Solution #1

Create a method that builds Q objects to test the date in parts:

def _filter_by_date_field(self, fld_value, searchableColumn, outputQ):
    mon_val = -1
    Mons = ['jan','feb','mar','apr','may','jun','jul','aug','sep','oct','nov','dec']
    if len(fld_value) >= 3 and fld_value[0:3].lower() in Mons:
        mon_val = next(i for i,v in enumerate(Mons) \
                    if v == fld_value[0:3].lower())+1
        outputQ |= Q(**{searchableColumn+"__month" : mon_val})
    if len(fld_value) >= 6 and mon_val > -1 and \
              re.search(r'^\d{2}$', fld_value[4:6]):
        outputQ &= Q(**{searchableColumn+"__day" : fld_value[4:6]})
    if len(fld_value) >= 8 and mon_val > -1 and \
              re.search(r'^\d{4}$', fld_value[8:12]):
        outputQ &= Q(**{searchableColumn+"__year" : fld_value[8:12]})
    return outputQ

If the field is a DateField, the method is called like this:

 if isinstance(<ModelName>.model._meta.get_field(searchableColumn), DateField):
        outputQ |= self._filter_by_date_field(customSearch,\
                                              searchableColumn,outputQ)

Here is an example of the SQL Django builds for a complete date:

EXTRACT('month' FROM "get_appts_vw"."appt_date") = 3) AND
   EXTRACT('day' FROM "get_appts_vw"."appt_date") = 5 AND 
   "get_appts_vw"."appt_date" BETWEEN '2016-01-01'::date AND '2016-12-31'::date)

Solution #2

create a database view for models using CharField (where the original model used a DateField)

class ApptView(models.Model):
    appt_date = models.CharField(max_length=12)
            ….
class Meta:
    db_table = u'get_appts_vw' # view name

the view

   CREATE VIEW get_appts_vw AS
    select to_char(a.appt_date,'Mon DD, YYYY') as appt_date
    ….
    from appts

This works but still requires an extra() clause to do sorts

Solution #3

(I am using this)

from django.db.models import Lookup
from django.db.models.fields import DateField
from django.db.models import CharField, Transform

@DateField.register_lookup
class TextDate(Transform):
    lookup_name = 'txtdt'

    @property
    def output_field(self):
        return CharField()


@TextDate.register_lookup
class DateTextFilter(Lookup):
    lookup_name = 'dttxt'

    def as_postgresql(self, compiler, connection):
        lhs, lhs_params = compiler.compile(self.lhs.lhs)
        rhs, rhs_params = self.process_rhs(compiler, connection)
        params = lhs_params + rhs_params
        return 'to_char(%s,\'Mon DD, YYYY\') ~* %s ' % (lhs, rhs), params

called like this

from .CustomFilters import * # or whatever you name your custom filters module
....
    if isinstance(<ModelName>.model._meta.get_field(searchableColumn), DateField):
            outputQ |= Q(**{<column_name>+"__txtdt__dttxt" : <search_value>})

Your last one looks quite odd. I'd expect the `to_char(...)` call in the `TextDate` transform and then the query to be `column__txtdt__like=value` — Shai Berger, Apr 01 '16 at 05:42
@ShaiBerger, interested in your solution. I have seen Transforms that use a simple function (ABS, lower, etc) but I have not seen a case where the Transform is like what I need i.e., to_char(%s,\'Mon DD, YYYY\'). If you have an example, I would be very interested to see — MIkee, Apr 27 '16 at 18:38
you seem to have written it correctly... just try it. Feel free to ask if your solution doesn't work. There is little difference (for this matter) between a function with one argument, and a function with two arguments of which one is a constant. — Shai Berger, Apr 30 '16 at 10:30

How can I filter a DateField or a DateTimeField in Django using a string?

1 Answers1

Linked