Re: Annotate date intervals or ranges
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.
Re: Annotate date intervals or ranges
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 On Friday, 4 March 2016 10:08:47 UTC+11, Josh Smeaton wrote: > > 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. > -- 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-developers+unsubscr...@googlegroups.com. To post to this group, send email to django-developers@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/20ba3084-559a-49d9-b7ff-6de45639ded6%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
Re: Annotate date intervals or ranges
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. -- 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-developers+unsubscr...@googlegroups.com. To post to this group, send email to django-developers@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/948e7c5e-2d21-45db-bc1e-159558016d25%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
Re: Annotate date intervals or ranges
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
On 2 March 2016 at 11:44, Sam Peka wrote:
> 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?
>
> --
> 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-developers+unsubscr...@googlegroups.com.
> To post to this group, send email to django-developers@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.
>
--
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-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@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/CAMwjO1HTh4pFyWSDPXQ1L-_2QznoDo%3DDYUoELDDiKv5ASiYykg%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.
Annotate date intervals or ranges
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?
--
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-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@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.
