Django使用ExpressionWrapper在SQLite上报错Invalid connector for timedelta

Django的SQLite数据库后端仅支持对timedelta类型执行+/-运算，你代码中timezone.timedelta(days=1) * models.F('duration')的乘法操作触发了SQLite后端的限制，因此单元测试报错；而PostgreSQL后端原生支持该乘法操作，所以生产环境运行正常。

方案1（推荐，适配Django 3.2及以上版本） #

使用Django内置的跨数据库兼容函数MakeInterval生成时间间隔，自动适配PostgreSQL和SQLite的语法，无需手动判断数据库类型，修改后的filter_expiration方法代码如下：

from django.db.models.functions import MakeInterval

class AccessDurationQuerySet(models.QuerySet):

    def filter_expiration(self, is_expired: bool = False):
        """
        To filter whether AccessDuration already expired or yet.
        don't use the same `expiration_date` as expression key,
        because it will clash with `AccessDuration.expiration_date` property.
        """
        kwargs = {'_expiration_date__gte': Now()}
        if is_expired:
            del kwargs['_expiration_date__gte']
            kwargs['_expiration_date__lt'] = Now()

        return self.all().annotate(
            _expiration_date=models.ExpressionWrapper(
                models.F('lab_start_date') + MakeInterval(days=models.F('duration')),
                output_field=models.DateTimeField()
            )
        ).filter(**kwargs)

方案2（兼容Django 3.2以下版本） #

如果使用的Django版本低于3.2没有内置MakeInterval函数，可以通过判断当前数据库类型动态生成兼容的表达式，代码如下：

from django.db import connection

class AccessDurationQuerySet(models.QuerySet):

    def filter_expiration(self, is_expired: bool = False):
        """
        To filter whether AccessDuration already expired or yet.
        don't use the same `expiration_date` as expression key,
        because it will clash with `AccessDuration.expiration_date` property.
        """
        kwargs = {'_expiration_date__gte': Now()}
        if is_expired:
            del kwargs['_expiration_date__gte']
            kwargs['_expiration_date__lt'] = Now()

        if connection.vendor == 'sqlite':
            # SQLite适配：将天数转换为微秒直接相加
            expiration_expr = models.ExpressionWrapper(
                models.F('lab_start_date') + models.F('duration') * 86400 * 1000000,
                output_field=models.DateTimeField()
            )
        else:
            # PostgreSQL原有逻辑保持不变
            expiration_expr = models.ExpressionWrapper(
                models.F('lab_start_date') + (timezone.timedelta(days=1) * models.F('duration')),
                output_field=models.DateTimeField()
            )

        return self.all().annotate(
            _expiration_date=expiration_expr
        ).filter(**kwargs)

内容的提问来源于stack exchange，提问作者binpy