Comparing unix timestamp with date in Django ORM

Question

I am trying to fetch all records from a table on a particular date.

My url.py code is :

 url(r'^jobs/date/?P<date>.*',RunningJobsListApiView.as_view()),

Here is the code of my view to get all the records from the table.

class RunningJobsListApiView(generics.ListAPIView):
        queryset = LinuxJobTable.objects.annotate(status=Case(When(state=3, then=Value('Completed')),When(state=5, then=Value('Failed')),When(state=1, then=Value('Running')),default=Value('Unknown'),output_field=CharField(),),)
        serializer_class = JobInfoSerializer

Now, I want to filter the jobs for the particular date in url. But In my database date is in UNIX timestamp format(ie.1530773247).

How can I compare DateFormat(mm-dd-yyyy) with UNIX timestamp format saved in DB?

Answer 1

To get a UNIX timestamp from a string date representation, you first need to convert the string to a Python datetime with strptime(), and then call the timestamp() method on it. But since a single day comprises a range of timestamps, you need to do a range query between the start of the target day and the start of the next day.

Something like:

from datetime import datetime, timedelta

target_day = datetime.strptime(date, "%m-%d-%Y")
next_day = target_day + timedelta(days=1)
queryset = LinuxJobTable.objects.filter(timestamp__range=(
    int(target_day.timestamp()), 
    int(next_day.timestamp()) - 1  # since range is inclusive
))