How to get last six month data from date field - django

Question

I do have a model as below

class Employee(models.Model):
    employee_name = models.CharField(max_length=100)
    joining_Date = models.DateField(blank=False, null=False)

I want to get data for last six months (including current month) joined employee count month wise.

Example:

July : 12,
June : 10,
May : 8,
April : 16,
March : 13,
February : 10,

joining_Date stores data like "2022-07-22". How to get done this by having date field?

0sVoid · Accepted Answer · 2022-07-22T10:55:02.323

2

To get the count of new employees per month you will need to annotate and use Trunc to just get the month, see below:

from datetime import date
from dateutil.relativedelta import relativedelta

from django.db.models.functions import Trunc

six_months_ago = date.today() + relativedelta(months=-6)

employees_per_month = Employee.objects.filter(join_date__gte=six_months_ago)\
   .annotate(
      joining_month=Trunc('joining_date', 'month', output_field=DateField())
   ) \
   .order_by('joining_month') \
   .values('joining_month') \
   .annotate(employees=Count('joining_month'))

This will give you a queryset with the following structure:

<QuerySet [{'joining_month': datetime.date(2022, 6, 1), 'employees': 2},
    {'joining_month': datetime.date(2022, 7, 1), 'employees': 1}, ...

Edit To convert the QS into a flat dict:

employee_count_dict = {}
for employee_count in employees_per_month: 
   employee_count_dict[val['joining_month']] = val['employees']

edited Jul 22 '22 at 10:55

answered Jul 22 '22 at 10:45

0sVoid

2,547
1
10
25

Wouldn't join_data__lte return dates which are smaller than six months ago? And not dates from last six months? – PTomasz Jul 22 '22 at 10:53
getting error: from dateutil.relativedelta import relativedelta ModuleNotFoundError: No module named 'dateutil' – Saravanan Ragavan Jul 22 '22 at 23:39
Installed pip install python-dateutil. getting error: name 'DateField' is not defined – Saravanan Ragavan Jul 22 '22 at 23:59
Removed output field, its working.. thanks.. is there any ways to convert datetime.date(2022, 7, 1) to July in Queryset result? – Saravanan Ragavan Jul 23 '22 at 00:18
used {{ value|date:"M" }} to show month.. thanks a ton... its working! – Saravanan Ragavan Jul 23 '22 at 00:26
Good stuff! DateField is another django db models import, sorry I missed that. And putting that template code is good, better to preserve dates where possible imo – 0sVoid Jul 23 '22 at 06:25
In case If we want to filter a date between today to future day(example: 10 days from today), how to do it? – Saravanan Ragavan Jul 23 '22 at 14:08

oruchkin · Answer 2 · 2022-07-22T10:47:45.593

0

You can try to find maximum apropriate date, and filter by it

from datetime import date
from dateutil.relativedelta import relativedelta


def find_employee(request):
    today = date.today()
    six_months = today - relativedelta(months=6)
    emloyed_for_six_month = Employee.objects.filter(joining_Date__gte = six_months)

Your employee model should be named with a capital letter. It is conventional https://stackoverflow.com/a/386054/14632651

edited Jul 22 '22 at 10:47

answered Jul 22 '22 at 10:31

oruchkin

1,145
1
10
21

OP asked for queryset with annotated count of workers grouped by month they joined in from last six months. What you did is give op queryset of workers who joined from -infinity to today+six months so like all of them – PTomasz Jul 22 '22 at 10:40
@TrueGopnik, i dont see any mentioning about grouping in a qustion, i see `How to get last six month data from date field - django`, `I want to get data for last six month(including current month) joined employee count month wise.` this is exactly what my solution does. Also, what does `op` means? – oruchkin Jul 22 '22 at 10:43
No, it's not. If you want to get data from last six months you would do six_months_ago = today - relativedelta(months=6) and then Employee.objects.filter(joining_Date__gte = six_months_ago) – PTomasz Jul 22 '22 at 10:45
@TrueGopnik i changed plus sign, on minus – oruchkin Jul 22 '22 at 10:49
And OP means Original Poster, I meant person who asked this question – PTomasz Jul 22 '22 at 10:50

How to get last six month data from date field - django

2 Answers2

Linked