Django和SQLite：DurationField的算术运算

Question

我有以下模型。

class MyModel(models.Model):
    start = models.TimeField()
    finish = models.TimeField()
    penalty = models.IntegerField(blank=True, default=0)  # penalty in minutes

以下是在管理员中使用的查询集注释

def get_queryset(self, request):
    qs = super().get_queryset(request)
    qs = qs.annotate(
        total_time=ExpressionWrapper(
            F("finish") - F("start") + F("penalty") * 60,
            output_field=DurationField()
        )
    )
    return qs

当我检查 penalty != 0 的某行的注释值时，它会显示类似于 datetime.timedelta(0, 1800, 1800) 的内容（在此情况下 penalty 是30分钟）。
然而，我期望看到一个 timedelta 类型的值，如 datetime.timedelta(0, 3600)。请问有人可以解释一下吗？

我也尝试过：

def get_queryset(self, request):
    qs = super().get_queryset(request)
    qs = qs.annotate(
        duration=ExpressionWrapper(
            F("finish") - F("start"),
            output_field=DurationField()
        )
    )
    qs = qs.annotate(
        total_time=ExpressionWrapper(
            F("duration") + F("route_shortening_penalty") * 60,
            output_field=DurationField()
        )
    )
    return qs

甚至

def get_queryset(self, request):
    qs = super().get_queryset(request)
    qs = qs.annotate(
        duration=ExpressionWrapper(
            F("finish") - F("start"),
            output_field=DurationField()
        ),
        penalty_duration=ExpressionWrapper(
            F("penalty") * 60,
            output_field=DurationField()
        )
    )
    qs = qs.annotate(
        total_time=ExpressionWrapper(
            F("duration") + F("penalty_duration"),
            output_field=DurationField()
        )
    )
    return qs

这两者都会导致

Traceback (most recent call last):
  File "/home/dm/.virtualenvs/test/lib/python3.6/site-packages/django/core/handlers/exception.py", line 34, in inner
    response = get_response(request)
  File "/home/dm/.virtualenvs/test/lib/python3.6/site-packages/django/core/handlers/base.py", line 126, in _get_response
    response = self.process_exception_by_middleware(e, request)
  File "/home/dm/.virtualenvs/test/lib/python3.6/site-packages/django/core/handlers/base.py", line 124, in _get_response
    response = wrapped_callback(request, *callback_args, **callback_kwargs)
  File "/home/dm/.virtualenvs/test/lib/python3.6/site-packages/django/contrib/admin/options.py", line 604, in wrapper
    return self.admin_site.admin_view(view)(*args, **kwargs)
  File "/home/dm/.virtualenvs/test/lib/python3.6/site-packages/django/utils/decorators.py", line 142, in _wrapped_view
    response = view_func(request, *args, **kwargs)
  File "/home/dm/.virtualenvs/test/lib/python3.6/site-packages/django/views/decorators/cache.py", line 44, in _wrapped_view_func
    response = view_func(request, *args, **kwargs)
  File "/home/dm/.virtualenvs/test/lib/python3.6/site-packages/django/contrib/admin/sites.py", line 223, in inner
    return view(request, *args, **kwargs)
  File "/home/dm/.virtualenvs/test/lib/python3.6/site-packages/django/utils/decorators.py", line 45, in _wrapper
    return bound_method(*args, **kwargs)
  File "/home/dm/.virtualenvs/test/lib/python3.6/site-packages/django/utils/decorators.py", line 142, in _wrapped_view
    response = view_func(request, *args, **kwargs)
  File "/home/dm/.virtualenvs/test/lib/python3.6/site-packages/django/contrib/admin/options.py", line 1793, in changelist_view
    'selection_note': _('0 of %(cnt)s selected') % {'cnt': len(cl.result_list)},
  File "/home/dm/.virtualenvs/test/lib/python3.6/site-packages/django/db/models/query.py", line 250, in __len__
    self._fetch_all()
  File "/home/dm/.virtualenvs/test/lib/python3.6/site-packages/django/db/models/query.py", line 1186, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File "/home/dm/.virtualenvs/test/lib/python3.6/site-packages/django/db/models/query.py", line 63, in __iter__
    for row in compiler.results_iter(results):
  File "/home/dm/.virtualenvs/test/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 1009, in apply_converters
    value = converter(value, expression, connection)
  File "/home/dm/.virtualenvs/test/lib/python3.6/site-packages/django/db/backends/base/operations.py", line 573, in convert_durationfield_value
    return datetime.timedelta(0, 0, value)
TypeError: unsupported type for timedelta microseconds component: str

我使用的是Python 3.6.7、Django==2.1.7和SQLite version 3022000。

- Dušan Maďar

---

如果罚款是DurationField而不是IntegerField，这样会更有意义吧？ - John Gordon

@JohnGordon 也尝试过那个方法，请查看更新后的问题。 - Dušan Maďar

为什么不使用类属性而不是注释？ - p14z

@Pedro，因为无法按属性对changelist_view进行排序。我想根据注释结果进行排序。 - Dušan Maďar

我没有看到任何代码表明您尝试了不同的模型定义。我只看到了对get_queryset()的更改。 - John Gordon

Answer 1

如果您得到的是 datetime.timedelta(0, 1800, 1800) 而不是正确的 datetime.timedelta(0, 3600)，那么您已经接近成功了。

我猜想，1800秒是 F("finish") 和 F("start") 之间的差异，而1800微秒来自于 F("penalty") * 60。

将 penalty 从分钟转换为微秒，请使用 60*1000*1000 而不是 60 进行乘法运算。

total_time=ExpressionWrapper(
    F("finish") - F("start") + F("penalty") * 60000000,
    output_field=DurationField()
)

- Endre Both

---

好的，所以有效的方法是乘以 60000000（1秒=1000000微秒）。感谢您指导我正确的方向，请相应更新答案。 - Dušan Maďar

谢谢。我应该先让我的孩子们运行转换程序。 - Endre Both