CODEWITHSUNDEEP
pythondjangopython-3.x
Joel G Mathew
Joel G Mathew

Reputation: 8081

Unable to filter a queryset by using year and month

My model is:

class Procedure(models.Model):
    procid = models.AutoField(primary_key=True, unique=True)
    timestr = models.DateTimeField(default=timezone.now)
    template = models.ForeignKey(ProcedureTemplate, on_delete=models.CASCADE, blank=True, null=True)
    clinic = models.ForeignKey(Clinic, on_delete=models.CASCADE)
    doctor = models.ForeignKey(doctor, on_delete=models.SET_NULL, blank=True, null=True)
    customer = models.ForeignKey(customer, on_delete=models.CASCADE, null=False)

I am trying to filter a queryset by year and month on a model property.

If I filter by year:

procedures = Procedure.objects.filter(clinic = clinicobj, timestr__year=2020)
for proc in procedures:
    print(f'{proc.pk} {proc.timestr}')

I get:

66 2020-01-08 12:38:37.237585+00:00
67 2020-01-11 15:40:00.344492+00:00
68 2020-01-12 04:50:56.190794+00:00
69 2020-01-26 05:58:36.962205+00:00
70 2020-01-29 09:51:59.038017+00:00
71 2020-02-01 14:24:18.921779+00:00
72 2020-02-09 06:20:30.993496+00:00
73 2020-02-15 10:23:09.068201+00:00
74 2020-02-15 14:04:29.368066+00:00
75 2020-02-16 06:25:09.702327+00:00
76 2020-02-19 14:05:19.369457+00:00
77 2020-02-20 11:13:35.934392+00:00

However when I try to narrow it down by adding the month, I am getting no results. What's wrong here?

Procedure.objects.filter(clinic = clinicobj, timestr__year=2020, timestr__month=2)

<QuerySet []>

Procedure.objects.filter(clinic = clinicobj, timestr__year=2020).filter(clinic = clinicobj, timestr__month=2)

<QuerySet []>

Upvotes: 0

Views: 105

Answers (2)

jackotonye
jackotonye

Reputation: 3853

Switch to using Extract* and would also suggest checking the timezone used to save the datetime values.

With USE_TZ = True. Convert the datetime using (timezone.make_aware(value))

from django.db.models.functions import ExtractMonth, ExtractYear


procedures = (
    Procedure.objects
    .annotate(
        year=ExtractYear('timestr'), 
        month=ExtractMonth('timestr'),
    )
    .filter(
        clinic=clinicobj, 
        year=2020, 
        month=2,
    )
)
for proc in procedures:
    print(f'{proc.pk} {proc.timestr}')

Result

71 2020-02-01 14:24:18.921779+00:00
72 2020-02-09 06:20:30.993496+00:00
73 2020-02-15 10:23:09.068201+00:00
74 2020-02-15 14:04:29.368066+00:00
75 2020-02-16 06:25:09.702327+00:00
76 2020-02-19 14:05:19.369457+00:00
77 2020-02-20 11:13:35.934392+00:00

Upvotes: 0

kamilyrb
kamilyrb

Reputation: 2627

Depends on documentation

"When USE_TZ is True, datetime fields are converted to the current time zone before filtering. This requires time zone definitions in the database."

So if you use mysql, you can install pytz and load the time zone tables with mysql_tzinfo_to_sql.

Also you can follow databases document in django.I hope it helps you.

Upvotes: 1

Related Questions