如何注释日期时间的天数差异 [英] How to annotate a difference of datetime in days

问题描述

我有一个Booking模型，该模型具有start和end日期时间字段.我想知道预订涵盖多少天.我可以在Python中执行此操作，但需要此值以进行进一步的注释.

I have a Booking model that has start and end datetime fields. I want to know how many days a booking covers. I can do this in Python but I need this value for further annotations.

这是我尝试过的:

In [1]: Booking.objects.annotate(days=F('end')-F('start'))[0].days
Out[1]: datetime.timedelta(16, 50400)

这里有一些问题:

• 我想要天的整数(或其他我可以在计算中使用的数字类型)作为输出，而不是timedelta.在这里设置output_field并没有任何意义.
• 我的总和基于日期时间.像这样的减法，而没有消除时间，可能会导致整个天数减少.

• I want an integer (or other number type I can use in calculations) of days as the output, not a timedelta. Setting output_field doesn't do anything meaningful here.
• My sums are based on datetimes. Subtractions like this, without removing the time could lead to the whole number of days being off.

在Python中，我会执行(end.date() - start.date()).days + 1.我该如何在数据库中进行此操作，最好是通过ORM(例如数据库功能)进行操作，但是RawSQL足以将其发布出去?

In Python I would do (end.date() - start.date()).days + 1. How can I do that in-database, preferably through the ORM (eg database functions), but a RawSQL would suffice to get this out the door?

推荐答案

我编写了一些数据库函数来转换和截断日期，以解决PostgreSQL下的两个问题.我正在使用的DATE_PART和DATE_TRUNC内部函数是特定于DB的☹

I've written a couple of database functions to cast and truncate the dates to solve both problems under PostgreSQL. The DATE_PART and DATE_TRUNC internal function I'm using are DB-specific ☹

from django.db.models import Func

class DiffDays(Func):
    function = 'DATE_PART'
    template = "%(function)s('day', %(expressions)s)"

class CastDate(Func):
    function = 'date_trunc'
    template = "%(function)s('day', %(expressions)s)"

那我可以:

In [25]: Booking.objects.annotate(days=DiffDays(CastDate(F('end'))-CastDate(F('start'))) + 1)[0].days
Out[25]: 18.0

这篇关于如何注释日期时间的天数差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！