Count value per day for last 7 days

Using Django
1

Hello ,

I am trying to query database for value occurrence count the sum per day and have result for the last 7 days per each day .

the value is pass_event_type and the date reference in model is event_stamp

my model as follows
    class PassEvents(models.Model):

    pass_event_type = models.CharField(max_length=200)
    event_stamp = models.DateTimeField(auto_now_add=True)
    user_related_event = models.CharField(max_length=200)
    ip_source = models.CharField(max_length=16)
    user_browser = models.CharField(max_length=200)
    user_os = models.CharField(max_length=200)
    user_agent = models.CharField(max_length=200)
    user_reset_reason = models.TextField(max_length=500,default=None, null=True, blank=True)

    def __str__(self):
        return self.user_related_event

The query i tried in views is as follows
from django.db.models.functions import (
ExtractDay, ExtractMonth, ExtractQuarter, ExtractWeek,
ExtractIsoWeekDay, ExtractWeekDay, ExtractIsoYear, ExtractYear,)
from django.db.models import Count, Sum

context[‘change_pass_line’] = PassEvents.objects.annotate(day=ExtractWeekDay(‘event_stamp’)).values(‘event_stamp’).annotate(count=Count(pass_event_type__exact=‘change password’)).values(‘day’,‘count’)

Please advice

Thanks

2

One of the first issues to clarify is the nature of the DateTimeField being used for event_stamp, and how it relates to your requirements.
The date and time components are stored in UTC. If you just compare by the extracted date, your “days” are going to be based on UTC. If your local time zone is not UTC, then the rows you’re selecting are not going to match up by date.

For example, right now I’m in EDT (Eastern Daylight Time). This is UTC-4. It’s 11:30 PM on 18 May. However, by UTC, it’s 3:30 AM, 19 May. If one of these tracked events were to occur right now, on which date do you want it shown?