When I use created_at__range to specify a range, the range is shifted by one day

I’m currently using the filter “created_at__range” to specify the first and last day of the month, but this code doesn’t reflect the data registered today.

        this_month = datetime.datetime.today()
        first_day = datetime.datetime(this_month.year, this_month.month, 1)
        this_month = this_month.strftime('%Y-%m-%d')
        first_day = first_day.strftime('%Y-%m-%d')
        time = obj.filter(created_at__range=(first_day, this_month)).aggregate(
            time=Sum('time'))['time']

Currently, I’m using timedelta(days=1) to add a day, but if I do this, for example, if the date is 3/31, it will be 4/1 and the tally will be wrong.

this_month = datetime.datetime.today() + timedelta(days=1)

Why is this happening? If anyone knows how to improve it, I’d appreciate it if you could let me know.

Answer

I assume that your field created_at is a DateTimeField. Quoting the warning from Django’s documentation

Warning

Filtering a DateTimeField with dates won’t include items on the last day, because the bounds are interpreted as “0am on the given date”. If pub_date was a DateTimeField, the above expression would be turned into this SQL:

SELECT ... WHERE pub_date BETWEEN '2005-01-01 00:00:00' and '2005-03-31 00:00:00';

Generally speaking, you can’t mix dates and datetimes.

I would like to add why even convert the datetime to a string simply use the object in your query:

this_month = datetime.datetime.today()
first_day = datetime.datetime(this_month.year, this_month.month, 1)
time = obj.filter(
    created_at__range=(first_day, this_month)
).aggregate(time=Sum('time'))['time']

Edit: In fact to make this a little easier for yourself and if there is no object that would have a datetime in the future, just let the ORM and database do a little more work if needed by just comparing the day and the year:

today = datetime.datetime.today()
time = obj.filter(
    created_at__year=today.year,
    created_at__month=today.month,
).aggregate(time=Sum('time'))['time']