How to Filter a Date of a Datetimefield in Django

Django - Filter objects by DateTimeField with a date range

When you filter DateTime field, the value which is used to compare must has type of Python DateTime. You can use datetime.strptime to format DateTime variable from string, then apply operators like __gte __lte ... to filter.

Django: Filter data between to dates | DatetimeField received a naive datetime

It is indeed correct that you make the from_date and to_date aware, but you do not use that in the __range check. You should work with:

def get_measurements(request):
    from_date = make_aware(datetime(2020, 10, 12, 15, 30, 0, 0))
    to_date = make_aware(datetime(2020, 10, 12, 16, 0, 0, 0))
    print(from_date)
    print(to_date)

    measurements = DriveMeasurements.objects.filter(
        #  use timezone aware datetimes ↓
        plc_timestamp__range=(from_date, to_date)
    )
    print(measurements.count())

How to compare DateTimeField with Date in Django filter funtion?

Several solutions:

1. Use date function provided by SQL(__date=):

query_result= MessageLogModel.objects.filter(received_date__date=myDate)

or:

query_result= MessageLogModel.objects.filter(received_date__date="2021-06-01")

2. Use __range=:

query_result= MessageLogModel.objects.filter(received_date__range=(myDate, myDate + timedelta(days=1)))

3. Use __startswith= or __contains=, similar usage to __date=
4. Use __gt= and __lt=, similar to __range=

All of the options above are supposing you're using the same timezone between the certain day and the data stored in database, if not, let's say if you saved datetime field in UTC while the timezone of your variable myDate is not UTC, then you probably need to convert a pair of datetime range in UTC first, and then query database by using range or gt, lt filter

Django: datetime filter by date ignoring time

From Django 1.9 you can use the __date field lookup, exactly as you have mentioned in your question. For older versions, you will have to do with the other methods.

e.g.

Entry.objects.filter(start__date=datetime.date(2005, 1, 1))
Entry.objects.filter(start__date__gt=datetime.date(2005, 1, 1))

How can I filter a DateField or a DateTimeField in Django using a string?

Solution #1

Create a method that builds Q objects to test the date in parts:

def _filter_by_date_field(self, fld_value, searchableColumn, outputQ):
    mon_val = -1
    Mons = ['jan','feb','mar','apr','may','jun','jul','aug','sep','oct','nov','dec']
    if len(fld_value) >= 3 and fld_value[0:3].lower() in Mons:
        mon_val = next(i for i,v in enumerate(Mons) \
                    if v == fld_value[0:3].lower())+1
        outputQ |= Q(**{searchableColumn+"__month" : mon_val})
    if len(fld_value) >= 6 and mon_val > -1 and \
              re.search(r'^\d{2}$', fld_value[4:6]):
        outputQ &= Q(**{searchableColumn+"__day" : fld_value[4:6]})
    if len(fld_value) >= 8 and mon_val > -1 and \
              re.search(r'^\d{4}$', fld_value[8:12]):
        outputQ &= Q(**{searchableColumn+"__year" : fld_value[8:12]})
    return outputQ

If the field is a DateField, the method is called like this:

 if isinstance(<ModelName>.model._meta.get_field(searchableColumn), DateField):
        outputQ |= self._filter_by_date_field(customSearch,\
                                              searchableColumn,outputQ)

Here is an example of the SQL Django builds for a complete date:

EXTRACT('month' FROM "get_appts_vw"."appt_date") = 3) AND
   EXTRACT('day' FROM "get_appts_vw"."appt_date") = 5 AND 
   "get_appts_vw"."appt_date" BETWEEN '2016-01-01'::date AND '2016-12-31'::date)

Solution #2

create a database view for models using CharField (where the original model used a DateField)

class ApptView(models.Model):
    appt_date = models.CharField(max_length=12)
            ….
class Meta:
    db_table = u'get_appts_vw' # view name

the view

   CREATE VIEW get_appts_vw AS
    select to_char(a.appt_date,'Mon DD, YYYY') as appt_date
    ….
    from appts

This works but still requires an extra() clause to do sorts

Solution #3

(I am using this)

from django.db.models import Lookup
from django.db.models.fields import DateField
from django.db.models import CharField, Transform

@DateField.register_lookup
class TextDate(Transform):
    lookup_name = 'txtdt'

    @property
    def output_field(self):
        return CharField()

@TextDate.register_lookup
class DateTextFilter(Lookup):
    lookup_name = 'dttxt'

    def as_postgresql(self, compiler, connection):
        lhs, lhs_params = compiler.compile(self.lhs.lhs)
        rhs, rhs_params = self.process_rhs(compiler, connection)
        params = lhs_params + rhs_params
        return 'to_char(%s,\'Mon DD, YYYY\') ~* %s ' % (lhs, rhs), params

called like this

from .CustomFilters import * # or whatever you name your custom filters module
....
    if isinstance(<ModelName>.model._meta.get_field(searchableColumn), DateField):
            outputQ |= Q(**{<column_name>+"__txtdt__dttxt" : <search_value>})

unable to filter() users with their date_joined field

So after playing with code for some time what I understood is all the ways that I have tried earlier where returning zero beaccuse 'date_joined" is a DateTimeField, therefore, in the code below

all_users_bydate = CustomUser.objects.all().filter(date_joined= datetime.today().date()).count()

the time is not getting matched hence, the count was being returned as 0.

So what I did is I used __contains to filter the date. So, this checks whether today's date is contained in the 'date_joined' field. This returns the correct count, i.e. 2.

 all_users_bydate = CustomUser.objects.all().filter(date_joined__contains= datetime.today().date()).count()

---

Related Topics