how to add a datetimefield to admin changelist's search_fields

Question

In a model's changelist page on admin, I added a DateTimeField "birth" to search_fields.

class DataLog(models.Model):
    id = models.AutoField(primary_key=True, verbose_name="idd")
    birth = models.DateTimeField(auto_now_add=True, verbose_name="create_time") 

class DataLogAdmin(admin.ModelAdmin):
    search_fields = ['id', 'birth', ]

it works well when i add it. But a error comes out today:

Warning at /admin/data/log/
    Incorrect datetime value: '%2011-07-12%' for column 'birth' at row 1
    Django Version:     1.2.3
    Exception Location: /usr/lib/python2.5/warnings.py in warn_explicit, line 102

this is the db sql:

u'SELECT COUNT(*) FROM `data_log` WHERE (`data_log`.`id` LIKE %2011-07-12% OR `data_log`.`birth` LIKE %2011-07-12% )'

I'v edited my django's project a lot since then.

so i dont know what cause this.

here is my question:

insert a DatetimeField to search_fields supported by django?
what's the correct way to do this?
any idea about my error?

New progress:

I tested that sql.

In Mysql, it runs well. ( both mysql's commandline and Django's dbshell )

mysql> SELECT COUNT(*) FROM `data_log` WHERE (`data_log`.`id` LIKE '%07-19%' OR `data_log`.`birth` LIKE '%07-19%' );
+----------+
| COUNT(*) |
+----------+
|        3 | 
+----------+
1 row in set, 1 warning (0.00 sec)

but in django shell, the same error:

User.objects.raw("SELECT COUNT(*) FROM `data_log` WHERE (`data_log`.`id` LIKE '%%07-19%%' OR `data_log`.`birth` LIKE '%%07-19%%' )")[0]

Traceback (most recent call last):
 File "<console>", line 1, in <module>
 File "/usr/lib/python2.5/site-packages/django/db/models/query.py", line 1379, in __getitem__
   return list(self)[k]
 File "/usr/lib/python2.5/site-packages/django/db/models/query.py", line 1372, in __iter__
   for row in self.query:
 File "/usr/lib/python2.5/site-packages/django/db/models/sql/query.py", line 73, in __iter__
   self._execute_query()
 File "/usr/lib/python2.5/site-packages/django/db/models/sql/query.py", line 87, in _execute_query
   self.cursor.execute(self.sql, self.params)
 File "/usr/lib/python2.5/site-packages/django/db/backends/util.py", line 15, in execute
   return self.cursor.execute(sql, params)
 File "/usr/lib/python2.5/site-packages/django/db/backends/mysql/base.py", line 86, in execute
   return self.cursor.execute(query, args)
 File "/var/lib/python-support/python2.5/MySQLdb/cursors.py", line 168, in execute
   if not self._defer_warnings: self._warning_check()
 File "/var/lib/python-support/python2.5/MySQLdb/cursors.py", line 82, in _warning_check
   warn(w[-1], self.Warning, 3)
 File "/usr/lib/python2.5/warnings.py", line 62, in warn
   globals)
 File "/usr/lib/python2.5/warnings.py", line 102, in warn_explicit
   raise message
Warning: Incorrect datetime value: '%07-19%' for column 'birth' at row 1

score 2 · Answer 1 · answered Jul 11 '14 at 17:30

According to:

https://docs.djangoproject.com/en/dev/ref/contrib/admin/#django.contrib.admin.ModelAdmin.search_fields

the search fields have to be some kind of text field, like CharField or TextField. You can do related lookups with foreignkeys and the like, but not with other field types.

As of Django 1.6, you can customize an admin search's get_search_results(). Thus, I left off the date field from search_fields when defining my admin class, and instead included this:

def get_search_results(self, request, queryset, search_term):
    # Try to filter by year of purchase
    try:
        int_term = int(search_term)
        queryset |= self.model.objects.filter(birth__year=int_term)
    except:
        pass
    return queryset, use_distinct

Of course, this only returns results by year, but you could definitely get fancy if you wanted. Not the ideal solution, really, but searching by date is a pretty non-intuitive process, once you start thinking about it.

fanlix · Accepted Answer · 2011-08-15T05:46:35.260

ok... I found the answer myself.

    Warning: Incorrect datetime value: '%123%' for column 'birth' at row 1

In mysql, using LIKE for datetime field works, but not Recommend. So, there is always the warning above. http://bugs.mysql.com/bug.php?id=38915
In Django, if we set "Debug=True" in settings.py, django handle this warning as an error.

so, all searches for that model fail.

I'll keep that DateTimeField in search_fields, but only used in non-Debug servers.

For my original question in title, the answer is:

just add DateTimeField to search_fields, it works for non-Debug servers

score 0 · Answer 3 · answered Jul 03 '19 at 10:14

date or datetime field is not in string type

admin.search_fields only support fields or related fields of string type

but still we can search date by overriding get_search_results

see django docs

# app/admin.py

class MaterialAdmin(admin.ModelAdmin):
    ....
    # Don't need to enter 'date'
    search_fields = ('supplier__name', 'service_name')

    def get_search_results(self, request, queryset, search_term):
        queryset, use_distinct = super().get_search_results(request, queryset, search_term)
        try:
            import datetime as dt
            import pytz
            date_obj = dt.datetime.strptime(search_term, '%Y年%m月%d日').date()
            # date_obj = dt.datetime.strptime(search_term, '%Y-%m-%d').date() # choose your own date format
            # date_time_obj = dt.datetime.strptime(search_term,  '%Y-%m-%d %H:%M:%S') # if you want to search datetime field
        except ValueError:
            pass
        else:
            queryset |= self.model.objects.filter(date=date_obj)
            # queryset |= self.model.objects.filter(date=date_time_obj)     # if search datetime
        return queryset, use_distinct

Tested in Django 2.1.7

score 0 · Answer 4 · answered Dec 14 '22 at 11:34

For django 4.* to add DateField or DateTimeField to search_fields is available by default. just follow the steps:

in admin.py

Just add your DateField or DateTimeField in search_fields like: search_fields = ['name', 'issue_date']
in Django admin search input enter the date format like:

2022-12-14 or 2022 ( to search only by year ) or 2022-12 ( to search only by year & Month) or 2022-12-14 ( to search only by year, Month & date)

how to add a datetimefield to admin changelist's search_fields

4 Answers4