Django - SQL Query - Timestamp

Question

Can anyone turn me to a tutorial, code or some kind of resource that will help me out with the following problem.

I have a table in a mySQL database. It contains an ID, Timestamp, another ID and a value. I'm passing it the 'main' ID which can uniquely identify a piece of data. However, I want to do a time search on this piece of data(therefore using the timestamp field). Therefore what would be ideal is to say: between the hours of 12 and 1, show me all the values logged for ID = 1987.

How would I go about querying this in Django? I know in mySQL it'd be something like less than/greater than etc... but how would I go about doing this in Django? i've been using Object.Filter for most of database handling so far. Finally, I'd like to stress that I'm new to Django and I'm genuinely stumped!

Answer 1

If the table in question maps to a Django model MyModel, e.g.

class MyModel(models.Model):
    ...
    primaryid = ...
    timestamp = ...
    secondaryid = ...
    valuefield = ...

then you can use

MyModel.objects.filter(
                       primaryid=1987
                      ).exclude(
                       timestamp__lt=<min_timestamp>
                      ).exclude(
                       timestamp__gt=<max_timestamp>
                      ).values_list('valuefield', flat=True)

This selects entries with the primaryid 1987, with timestamp values between <min_timestamp> and <max_timestamp>, and returns the corresponding values in a list.

Update: Corrected bug in query (filter -> exclude).

Answer 2

I don't think Vinay Sajip's answer is correct. The closest correct variant based on his code is:

MyModel.objects.filter(
                       primaryid=1987
                      ).exclude(
                       timestamp__lt=min_timestamp
                      ).exclude(
                       timestamp__gt=max_timestamp
                      ).values_list('valuefield', flat=True)

That's "exclude the ones less than the minimum timestamp and exclude the ones greater than the maximum timestamp." Alternatively, you can do this:

MyModel.objects.filter(
                       primaryid=1987
                      ).filter(
                       timestamp__gte=min_timestamp
                      ).exclude(
                       timestamp__gte=max_timestamp
                      ).values_list('valuefield', flat=True)

exclude() and filter() are opposites: exclude() omits the identified rows and filter() includes them. You can use a combination of them to include/exclude whichever you prefer. In your case, you want to exclude() those below your minimum time stamp and to exclude() those above your maximum time stamp.

Here is the documentation on chaining QuerySet filters.