Filter by CharField pretending it is DateField in Django ORM/mySql

I am working with a already-done mySQL Database using Django ORM and I need to filter rows by date - if it wasn't that dates are not in Date type but normal Varchar(20) stored as dd/mm/yyyy hh:mm(:ss). With a free query I would transform the field into date and I would use > and < operators to filter the results but before doing this I was wondering whether Django ORM provides a more elegant way to do so without writing raw SQL queries.

I look forward to any suggestion.

EDIT: my raw query would look like

SELECT * FROM table WHERE STR_TO_DATE(mydate,'%d/%m/%Y %H:%i') > STR_TO_DATE('30/12/2020 00:00', '%d/%m/%Y %H:%i')

Thank you.

Solution 1:

I will assume your model looks like this:

from django.db import models

class Event(models.Model):
    mydate = models.CharField(max_length=20)

    def __str__(self):
        return f'Event at {self.mydate}'

You can construct a Django query expression to represent this computation. This expression consists of:

• Func objects representing your STR_TO_DATE function calls.
• An F object representing your field name.
• A GreaterThan function to represent your > comparison.

from django.db.models import F, Func, Value
from django.db.models.lookups import GreaterThan
from .models import Event

# Create some events for this example
Event(mydate="29/12/2020 00:00").save()
Event(mydate="30/12/2020 00:00").save()
Event(mydate="31/12/2020 00:00").save()

class STR_TO_DATE(Func):
    "Lets us use the STR_TO_DATE() function from SQLite directly in Python"
    function = 'STR_TO_DATE'

# This Django query expression converts the mydate field
# from a string into a date, using the STR_TO_DATE function.
mydate = STR_TO_DATE(F('mydate'), Value('%d/%m/%Y %H:%i'))

# This Django query expression represents the value 30/12/2020
# as a date.
date_30_12_2020 = STR_TO_DATE(Value('30/12/2020 00:00'), Value('%d/%m/%Y %H:%i'))

# This Django query expression puts the other two together,
# creating a query like this: mydate < 30/12/2020
expr = GreaterThan(mydate, date_30_12_2020)

# Use the expression as a filter
events = Event.objects.filter(expr)
print(events)

# You can also use the annotate function to add a calculated
# column to your query...
events_with_date = Event.objects.annotate(date=mydate)

# Then, you just treat your calculated column like any other
# field in your database. This example uses a range filter
# (see https://docs.djangoproject.com/en/4.0/ref/models/querysets/#range)
events = events_with_date.filter(date__range=["2020-12-30", "2020-12-31"])
print(events)

I tested this answer with Django 4.0.1 and MySQL 8.0.