Django: how to concatenate the separated integer field (Year, month) as date range to filter the database

Question

In forms, there are two fields:

class Input(models.Model):
    start_year=models.CharField(max_length=100)
    start_month=models.CharField(max_length=100)
    end_year=models.CharField(max_length=100)
    end_month=models.CharField(max_length=100)
    ....

In sql database, there two columns: year ; month, ....

I want to based on what users have entered in form (start_year,start_month ; end_year,end_month) as a date range to filter in database (year, month).

XX.objects.filter(date_range=[]), or can I put in this data_range function?

Following are some related code if you need.

the app with form where user enter the data - views.py

def input(request):
    if request.method == 'POST':
        form = InputForm(request.POST)
        if form.is_valid():
            ...
            start_year=form.cleaned_data['start_year']
            start_month=form.cleaned_data['start_month']
            end_year=form.cleaned_data['end_year']
            end_month=form.cleaned_data['end_month']
            ...
            form.save()
            return redirect('FilterResult')

to filter the database based on user's entry - views.py

class XXXView(ListView):
    context_object_name = 'XXX'
    template_name = 'XXX.html'

    queryset = XXX.objects.all()
    start_year=self.request.query_params.get('start_year', None)  /*get from the form what the user has entered
    start_month=self.request.query_params.get('start_month', None)
    end_year=self.request.query_params.get('end_year', None)
    end_month=self.request.query_params.get('end_month', None)

    objects.filter(date_range=[.....]) /*how to concatenate the year and month to put here?

    if start_year,start_month,end_year,end_month are not None:
                      queryset=queryset.filter(start_month=start_month,start_year=start_year,end_year=end_year,end_month=end_year)

    sales=XXX.objects.filter(queryset).aggregate(Sum('sales'))

    def get_context_data(self, **kwargs):
        context = super(XXXView, self).get_context_data(**kwargs)
        context['input'] = Input.objects.order_by('-id')[:1]
        return context

@Burger King,sorry I don't quite get you mean, the "start" should be start_year&start_month, the "end" should be end_year&end_month. — Héléna, Oct 19 '15 at 05:38

score 2 · Accepted Answer · edited May 23 '17 at 12:33

If you use DateField instead of CharField (which I think you really should be doing because you are dealing with dates) like so:

class Input(models.Model):
    # This date has the month and the year. You can set the default day 
    # to the 1st of every month for consistency.
    startDate=models.DateField() 

    # This also has the end month and end year.
    endDate=models.DateField()

You can filter date objects by range by doing something along the lines of:

Input.objects.filter(startDate__range=["2011-01-01", "2011-01-31"])

Or if you want to filter by a specific month, you can do:

Input.objects.filter(endDate__year='2011', endDate__month='01')

See this post: Django database query: How to filter objects by date range? and this post: How do I subtract two dates in Django/Python? for more information.

I'm not too sure what you exactly want to accomplish but I'm sure that if you use a DateField instead of a CharField to save the dates, it would be much easier. I also suggest you read the documentation on DateField as well for more information (it can come in handy in your situation).

thank you. But I think my case is somewhat different. Because in sql database, there are 2 separated columns: "year" , "month". If I put Input.objects.filter(startDate__range=["StartDate", "EndDate"]), how it checks with the 2 separated year and month rows in database? — Héléna, Oct 19 '15 at 08:26

Django: how to concatenate the separated integer field (Year, month) as date range to filter the database

1 Answers1