Django ORM filter on datetime understands entries with a 'Z' offset, but not '+0000' offset

I have a MySQL database that mirrors a salesforce database. My table has a column for "createddate" that is contains ISO-8601 datetime entries. Some of the entries have an offset of "Z" and some of the entries have an offset of "+0000" like this:

2025-01-20T17:18:18.000Z

2025-01-20T18:11:10.000Z

2025-01-20T17:27:55.000+0000

2025-01-20T17:29:46.000Z

2025-01-20T17:28:19.000+0000

When I attempt to filter on a certain date, the filter ONLY returns lines that have a "Z" offset. lines with "+0000" are not returned.

My filter code looks like:

receipts = Receipt.objects.filter(createddate__date='2025-01-20').count()

As far as I can tell, both formats conform to ISO-8601.

I do have USE_TZ set to true in my settings.py

the field is configured in models.py like:

createddate = models.CharField(db_column='CreatedDate', max_length=28, blank=True, null=True)

Relatively new to django and its ORM, I'm currently working around with a raw SQL query but I'd rather do it natively if possible.

If this isn't mission critical and you just need it to be generally correct, try the following:

receipts = Receipt.objects.filter(createddate__contains='2025-01-20').count()

This should work, as the db is returning some type of string/VARCHAR element.

This won't account for timezone offsets, so will only contain objects created on a given date as of UTC time.

A better method would be to define a QuerySet to incorporate some parsing logic, such as using strptime to convert the objects to python datetime objects and then apply your logic on those.

You can convert string objects to real dates using the django ORM Cast function. I would do something like this.

import datetime
from django.db.models import functions

from_date = datetime.datetime.now().date() - datetime.timedelta(days=1)
to_date = datetime.datetime.now().date()

queryset = (
    Receipt.objects
    .alias(
        created_at=functions.Cast(
            expression='createddate',
            #  Or models.DateField().
            output_field=models.DateTimeField(),
        )
    )
    .filter(
        #  When converting to `models.DateField()`, remove `__date` lookup.
        created_at__date__lte=from_date,
        created_at__date__gte=to_date,
    )
)

Or by using range lookup:

queryset = (
    Receipt.objects
    .alias(
        created_at=functions.Cast(
            expression='createddate',
            output_field=models.DateTimeField(),
        )
    )
    .filter(
        created_at__date__range=(
            datetime.datetime.from_date,
            datetime.datetime.to_date,
        ),
    )
)

Or you can use only certain filters: only gt, or only lte, and so on. Then just call queryset.count(). You can also read this section in the documentation.

Back to Top