Annotate date intervals or ranges

[Sam Peka]

It would be great if there was a way within the standard Queryset api to annotate ranges of dates. The use case is that it would remove the need to resort to RawSQL when grouping things by date ranges, such as the day of the month. I know the postgres extras package has a DateRangeField, but it's surprisingly difficult to do this dynamically.

So I'd propose something like this:

queryset.annotate(day_created=DateRange('day', 'created_date'))

Which for Postgres would equate to:

SELECT date_trunc('day', "created_date") as day_created from ...

Is there a technical reason why this hasn't already been done? Or has there not been much of a need for it?

[Marc Tamlyn]

Probably just because there hasn't been an immediate need, and because it working on every database the same way could be... awkward. When you have dates, you also have to think about the timezone implications as well - which timezone do you need that date in?

It's also worth mentioning that if you don't care about those subtleties for your application, you can use Func() to do this:

.annotate(day_created=Func(Value('day'), 'created_date', function='date_trunc'))

https://docs.djangoproject.com/en/1.9/ref/models/expressions/#func-expressions

--
You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-develop...@googlegroups.com.
To post to this group, send email to django-d...@googlegroups.com.
Visit this group at https://groups.google.com/group/django-developers.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/fe7dcfc9-cbb5-439b-bf08-f45e854a99c6%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Josh Smeaton]

A somewhat related ticket: https://code.djangoproject.com/ticket/25774 which attempts to make current datetime transforms/expressions public. These focus mainly on EXTRACT() type transformations, but there's certainly a place for date_trunc type transformations too. Feel free to create a ticket proposing such expressions if one does not already exist.

[Josh Smeaton]

FYI I'm implementing date_trunc based transforms in this patch: https://github.com/django/django/pull/6243

Since the transform names "__month" "__year" etc are already taken by the Extract based transforms I've not yet implemented a lookup_name so that they can be used on the left hand side of filters (.filter(created__monthtrunc=..). If there is a decent proposal for trunc based lookup names, it'll be very easy to add them on.

So here's my request for proposal. Are there any good lookup names that we can give trunc based date transformations? If there are no good suggestions before this is ready for commit, that's ok. We can always add lookup_name properties at a later datetime (sorry).

Cheers

[Shai Berger]

On Monday 07 March 2016 12:20:59 Josh Smeaton wrote:
> FYI I'm implementing date_trunc based transforms in this
> patch: https://github.com/django/django/pull/6243
>
> Since the transform names "__month" "__year" etc are already taken by the
> Extract based transforms I've not yet implemented a lookup_name so that
> they can be used on the left hand side of filters
> (.filter(created__monthtrunc=..). If there is a decent proposal for trunc
> based lookup names, it'll be very easy to add them on.
>

I find the name "trunc" is SQL-technical and opaque; and there's a need to
explain the difference between, say, the extract transform (where 'year' gives
an integer) and the trunc transforms which all give dates and datetimes,
corresponding to the start of the named period.

So, I suggest we use "start".

birthdate__yearstart (midnight, january first on the birth date's year)

event__daystart (midnight before the event)

etc.