4

I'm creating a Django app that needs to perform date differences. Given a model with a start_date and end_date, both DateFields, on Postgres this works as a command like:

model.objects.annotate(difference=F(end_date)-F(start_date))

will work perfectly fine. However, on an SQLite backend this doesn't work as well.

Rather than giving a timedelta (or similar), it returns a string, which is approximately the difference in years. However, SQLite has a command julianday() which converts the date to a "julian day" which can at least be used to get a difference in days.

For example, on the dbshell, this will give the right number of days difference:

SELECT julianday(end_date) - julianday(start_date) FROM 'appname'.'model';

Is there a way I can:

  1. Check the database backend - eg. is SQLite or not?

  2. and, then if it is SQLite wrap it in the julianday function?

Alasdair
  • 298,606
  • 55
  • 578
  • 516

1 Answers1

4

I actually got around this by making a custom database function that only does anything on SQLite, like that below:

from django.db.models.expressions import Func
# SQLite function to force a date time subtraction to come out correctly.
# This just returns the expression on every other database backend.
class ForceDate(Func):
    function = ''
    template = "%(expressions)s"
    def __init__(self, expression, **extra):
        self.__expression = expression
        super(ForceDate, self).__init__(expression, **extra)

    def as_sqlite(self, compiler, connection):
        self.function = 'julianday'
        self.template = 'coalesce(%(function)s(%(expressions)s),julianday())*24*60*60*1000*1000' # Convert julian day to microseconds as used by Django DurationField
        return super(ForceDate, self).as_sql(compiler, connection)

Then, used an ExpressionWrapper in the code to coerce the difference into a DurationField (note this only works in Django 1.8)

ExpressionWrapper(db.ForceDate(F(a))-db.ForceDate(F(b)), output_field=DurationField())