CODEWITHSUNDEEP
mysqldjango
Prabhakaran
Prabhakaran

Reputation: 4013

Django filter based on start date and end date for single day

I have a query 

start_date = '2019-11-17'
end_date = '2019-11-18'

events = Event.objects.filter(start_date__gte=start_date,end_date__lte=end_date)

This doesn't return any result.. I tried direct mysql query, even this doesn't return any results,

SELECT * FROM `events` where start_date >= '2019-11-17' and end_date <= '2019-11-18'

Any idea where I am wrong?

In my database I have these 3 rows,

enter image description here

This works,

SELECT * FROM `events` where start_date > '2019-11-17' and end_date < '2019-11-19'

But why not <= '2019-11-18'?? any idea?

I managed to add 1 day to the end_date to resolve it.

MY SOLUTION

start_date = datetime.fromtimestamp(start_date/1000.0)
end_date = datetime.fromtimestamp(end_date/1000.0)

start_date = start_date.strftime('%Y-%m-%d')
end_date = (end_date + timedelta(days=1)).strftime('%Y-%m-%d')

NOW THIS WORKS

events = Event.objects.filter(start_date__gt=start_date,end_date__lt=end_date)

Upvotes: 1

Views: 1510

Answers (2)

Night Owl
Night Owl

Reputation: 136

Or you can use django Queryset to filter a datetime field just by the date:

events = Event.objects.filter(start_date__date__gte=start_date, end_date__date__lte=end_date)

https://docs.djangoproject.com/en/2.2/ref/models/querysets/#date

Upvotes: 1

Ed Bangga
Ed Bangga

Reputation: 13006

your date is beyond 2019-11-18, cast your datetime columns as date

SELECT * FROM `events` where cast(start_date as date) >= '2019-11-17' and cast(end_date as date) <= '2019-11-18'

if you are going to evaluate below expression, it will return 0

select cast('2019-11-18 16:23:01' as datetime)  <= '2018-11-18'

Upvotes: 1

Related Questions