[Answered ]-Django raw sql/postgres time zone confusion

by

2👍

✅

First, add related_name='votes' into your foreign key to location, for better control, now using location model you can do:

from django.db.models import Count, Case, Sum, When, IntegerField
from django.db.models.expressions import DateTime

queryset = location.objects.annotate(
    datetimes=DateTime('votes__timestamp', 'hour', tz),
    positive_votes=Count(Case(
        When(votes__value__gt=0, then=1),
        default=None,
        output_field=IntegerField())),
    indifferent_votes=Count(Case(
        When(votes__value=0, then=1),
        default=None,
        output_field=IntegerField())),
    negative_votes=Count(Case(
        When(votes__value__lt=0, then=1),
        default=None,
        output_field=IntegerField())),
    score=Sum(Case(
        When(votes__value__lt=0, then=-4),
        When(votes__value=0, then=1),
        When(votes__value__gt=0, then=2),
        output_field=IntegerField())),
    ).values_list('datetimes', 'positive_votes', 'indifferent_votes', 'negative_votes', 'score').distinct().order_by('datetimes')

That will generate statistics for each of location. You can of course filter it to any location or time range.

👤GwynBleidD

0👍

If the datetime fields you are dealing will allow nulls you can work around https://code.djangoproject.com/ticket/25937 with the following:

Potato.objects.annotate(
    time=Coalesce(
        TruncMonth('removed', tzinfo=timezone.UTC()),
        Value(datetime.min.replace(tzinfo=timezone.UTC()),
    ).values('time').annotate(c=Count('pk'))

This replaces the NULL times with an easy to spot sentinel. if you were already using datetime.min, you’ll have to come up with something else.

I’m using this in production, but I’ve found that where TruncMonth() on it’s own would give you local time, when you put Coalesce() around it you can have only naive or UTC.

👤Aaron McMillin

Leave a comment