Filter datetime field python

Question

Json receiving python:

{
    "str_ini": "2020-06-05",
    "fnsh_date": "2020-06-20",
}

I use str_ini and fnsh_date as a parameter to build my query.

OrderModel.py:

fservice = models.DateTimeField(db_column='FService') # (In DB this field is datetime)
ffinishservice = models.DateTimeField(db_column='FFinishService') # (In DB this field is datetime)

Views.py:

pedidoparams = request.data
start_date = pedidoparams.get("str_ini")
finish_date = pedidoparams.get("fnsh_date")
order = (
    OrderModel.objects.values("zone__zone")
    .filter(fservice__gte=fecha_inicio)
    .filter(ffinishservice__lte=fecha_final)
    .annotate(order_activate=Count("cctt__id"))
)
print("query: ", str(pedidos.query))

And python print this query:

query:  SELECT `zone`.`Zone` AS `Zone`, COUNT(`order`.`cctt_id`) AS `order_activate` FROM `order` INNER JOIN `zone` ON (`order`.`Zone_Id` = `zone`.`Id`) WHERE (`order`.`fservice` >= 2020-06-05 00:00:00 AND `order`.`ffinishservice` <= 2020-06-20 00:00:00) GROUP BY `zone`.`Zone`

My question is: why do you add hours, minutes and seconds to the query? In case of receiving only the dates, would it be possible that in the query it was:

... Where (`order`.`fservice` >= 2020-06-05 00:00:00 AND `order`.`ffinishservice` <= 2020-06-20 23:59:59)

score 0 · Answer 1 · answered Aug 07 '20 at 10:30

The field in the database is a datetime, so Django coerces dates into datetimes; the default time for a datetime from a date is midnight.

If your inputs are dates, you can either

add one day to the end date, and use lt instead of lte
combine the end date with 23:59:59 and keep using lte (which could miss a leap second, but that likely doesn't matter)

from django.utils.dateparse import parse_date
import datetime


start_date = parse_date(pedidoparams["str_ini"])
finish_date = parse_date(pedidoparams["fnsh_date"])
start_datetime = datetime.datetime.combine(start_date, datetime.time())
end_datetime = datetime.datetime.combine(end_date, datetime.time(23, 59, 59))

order = (
    OrderModel.objects.values("zone__zone")
    .filter(fservice__gte=start_datetime)
    .filter(ffinishservice__lte=end_datetime)
    .annotate(order_activate=Count("cctt__id"))
)

For another service, I first do `from datetime import datetime`, your solution gives me an error, what I do is the next: `start_datetime = datetime.combine(start_date, datetime.time()) end_datetime = datetime.combine(end_date, datetime.time(23, 59, 59))` but return: descriptor 'time' of 'datetime.datetime' object needs an argument If I make: `start_datetime = datetime.combine(start_date, time()) end_datetime = datetime.combine(end_date, time(23, 59, 59))`return: name 'time' is not defined — Manuel, Aug 07 '20 at 11:52

Filter datetime field python

1 Answers1