Properly make a BETWEEN clause in DjangoORM

Question

Im having problem querying the desired result in my database using DjangoORM.

Here is my model.

class HeatWatchList(models.Model):
    user = models.ForeignKey(settings.AUTH_USER_MODEL,
                   related_name='watchlist_users', on_delete=models.CASCADE)

    heat = models.OneToOneField(Heat, related_name='heat_watch_list', 
                   on_delete=models.CASCADE)

    next_date_from = models.DateTimeField()
    next_date_to = models.DateTimeField()

I would like to get all record where today's date is BETWEEN next_date_from and next_date_to fields.

And i can easily do this in raw sql:

SELECT 
    *
FROM
    farm_management_db.heat_heatwatchlist
WHERE
    DATE(now()) BETWEEN DATE(next_date_from) AND DATE(next_date_to);

This is what i tried so far but i ain't getting any records:

HeatWatchList.objects.filter(
                next_date_from__date__gte=datetime.now().date(),
                next_date_to__date__lte=datetime.now().date()
            )    

Answer 1

You should try the following code instead(by removing the __date):

HeatWatchList.objects.filter(
                next_date_from__gte=datetime.now().date(),
                next_date_to__lte='2017-01-23'
            )   

Answer 2

You could add a function to your model.

def is_between_date(self): if Timezone.zone() > next_date_from and timezone.zone() < next_date_to: return True return False

Answer 3

You can do this(by removing the __date) and use datetime.combine

from datetime import  datetime, time

HeatWatchList.objects.filter(
                next_date_from__gte=datetime.combine(datetime.now().date(), time.min),
                next_date_to__lte=datetime.combine(datetime.now().date(), time.max)
            )