Django Custom Lookups

23 April 2019

By default Django has a date lookup that support timezones. It actually will wrap your field with CONVERT_TZ in case USE_TZ is True. This is pretty awesome unless you have timezones table empty because this call will return Null.

mysql> SELECT CONVERT_TZ('2019-01-01 12:00:00','UTC','UTC');
+-----------------------------------------------+
| CONVERT_TZ('2019-01-01 12:00:00','UTC','UTC') |
+-----------------------------------------------+
| NULL                                          |
+-----------------------------------------------+
1 row in set (0.00 sec)

But even the following will work, It is not safe with Daylight saving timing. You can check this thread

mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+01:00');
+-----------------------------------------------------+
| CONVERT_TZ('2004-01-01 12:00:00','+00:00','+01:00') |
+-----------------------------------------------------+
| 2004-01-01 13:00:00                                 |
+-----------------------------------------------------+

Here is what i was doing

import datetime
from django.utils import timezone
from app.models import Incident

days = 1
last_x_days = (timezone.now() - datetime.timedelta(days)).strftime('%Y-%m-%d')
Incident.objects.filter(datetime__date=last_x_days).order_by('-datetime')

And this will create an SQL query like this

SELECT `app_incident`.`id`, `app_incident`.`name`, `app_incident`.`uri`, `app_incident`.`status`, `app_incident`.`datetime`, `app_incident`.`created_at`, `app_incident`.`updated_at` FROM `app_incident` WHERE DATE(CONVERT_TZ(`app_incident`.`datetime`, 'UTC', 'UTC')) = '2019-04-22' ORDER BY `app_incident`.`datetime` DESC;

But the previous SQL Query will never work unless i have timezones table. so I was thinking to make my application work in case timezones table exist or not. so i created another custom lookup that will remove the CONVERT_TZ entirely.

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


class DateEqLookup(Lookup):
    """A custom lookup, that lets you query DateField and DateTimeFields by a date"""

    lookup_name = 'date_eq'

    def as_sql(self, compiler, connection):
        lhs, lhs_params = self.process_lhs(compiler, connection)
        rhs, rhs_params = self.process_rhs(compiler, connection)

        params = lhs_params + rhs_params
        return 'DATE(%s) = DATE(%s)' % (lhs, rhs), params

DateField.register_lookup(DateEqLookup)
DateTimeField.register_lookup(DateEqLookup)

import datetime
from django.utils import timezone
from app.models import Incident

days = 1
last_x_days = (timezone.now() - datetime.timedelta(days))
Incident.objects.filter(datetime__date_eq=last_x_days).order_by('-datetime')

and here is the resulting query

SELECT `app_incident`.`id`, `app_incident`.`name`, `app_incident`.`uri`, `app_incident`.`status`, `app_incident`.`datetime`, `app_incident`.`created_at`, `app_incident`.`updated_at` FROM `app_incident` WHERE DATE(`app_incident`.`datetime`) = DATE('2019-04-17 20:44:37.697357') ORDER BY `app_incident`.`datetime` DESC;

Now i can switch between __date_eq and __date lookups and the application will always work in case timezone table exists or not.

For more informations about custom lookups please check django documentations