3

I have a datetime field in Django, and I want to filter this based on time. I don't care about the particular date, but I want to find all transactions before 7:30, for example.

I know I can filter by hour and minute such as:

Q(datetime__hour=7) & \
Q(datetime__minute=30)

However, this would find all transactions AT 7:30. You are also unable to apply gte or lte. i.e.

(Q(datetime__hour__lte=7) & \
Q(datetime__minute__lte=30)) | \
Q(datetime__hour__lt=7)

The only thing that appears to be a potential solution is to have many queries, such as:

(Q(datetime__hour=7) & \
(Q(datetime__minute=30) | \
 Q(datetime__minute=29) | \
 Q(datetime__minute=28) | \
 ....
 Q(datetime__minute=2) | \
 Q(datetime__minute=1) | \
 Q(datetime__minute=0))) | \
Q(datetime__hour=6) | \
Q(datetime__hour=5) | \
Q(datetime__hour=4) | \
Q(datetime__hour=3) | \
Q(datetime__hour=2) | \
Q(datetime__hour=1) | \
Q(datetime__hour=0)

But this seems ridiculous.

Anyone have any ideas?

  • try this myClass.objects.filter(condition here).order_by('-datetime') – Wagh Jan 08 '15 at 16:54
  • possible duplicate of [How can I filter a date of a DateTimeField in Django?](http://stackoverflow.com/questions/1317714/how-can-i-filter-a-date-of-a-datetimefield-in-django) – XORcist Jan 08 '15 at 16:54
  • XORcist - Not a duplicate. That link is the total opposite. It wanted to filter by date, ignoring the time. I want to filter by time ignoring the date. –  Jan 08 '15 at 16:57
  • Gaurav - That will just order the results differently. That won't filter. –  Jan 08 '15 at 16:58
  • Why can't you use your lte solution? – nima Jan 08 '15 at 17:08
  • nima - Because it doesn't work and throws an error. –  Jan 09 '15 at 08:26

4 Answers4

1

Just split the datetime field into a date and a time field. Than you can filter on time only:

from datetime import combine

class MyModel(models.Model):
    start_date = models.DateField()
    start_time = models.TimeField()

    class Meta:
        ordering = ['start_date', 'start_time']

    def start_datetime(self):
        return combine(self.date, self.time)

I added the Meta.ordering and start_datetime model method to show that this model can present data in the same way a model with a DateTimeField can.

Now you can filter on time:

objects = MyModel.objects.filter(start_time__lt=time(7, 30))

Update

If you have a established project and many queries depend on having a normal DateTime field. Splitting the datetime into a date and time fields come a cost: Rewriting queries threw out your project. There is an alternative: Only add a time field and leave the datetime field untouched. A save method can add the time based on datetime. The down side is that you have duplicated data in your db. The good thing it solves your problem with minimal effort.

class MyModel(models.Model):
    start_datetime = models.DateTimeField()
    start_time = models.TimeField(blank=True)

    def save(self)
        self.start_time = self.start_datetime.time

All existing queries will be the same as before and filter on time only:

objects = MyModel.objects.filter(start_time__lt=time(7, 30))
allcaps
  • 10,945
  • 1
  • 33
  • 54
  • This _might_ be possible. However as this is an established project, and several external things rely on the data and how it is formatted, it also might not be... I shall check. I would say that this was the best solution so far, though. –  Jan 09 '15 at 08:28
  • Update: alternative for existing projects. – allcaps Jan 09 '15 at 09:51
  • Thanks allcaps. I think this is the best solution. –  Jan 09 '15 at 10:02
0

In Django 1.7+ you could use a transform to extract minute and hour from the timestamp. See https://docs.djangoproject.com/en/1.7/howto/custom-lookups/#a-simple-transformer-example for details.

akaariai
  • 714
  • 3
  • 6
  • Unfortunately, we are unable to migrate to 1.7 just yet. Should happen in the coming months though. –  Jan 09 '15 at 08:25
0

Not sure when the filter by time was introduced, but in recent versions of Django (4.0+) this is done by the following query syntax:

YourModel.objects.all().filter(datetime__hour__lte=7, datetime__minute__lte=30)

Where YourModel is the model you are filtering, and datetime is the field from that model.

renno
  • 2,659
  • 2
  • 27
  • 58
-1

Ideas:

  • use raw sql
  • (mem)cache your results (e.q.::pseudocode:: md5(sql_string) = sql_result).
  • create a middle table with 5 indexed integer columns (year, month, day, hour, minute). Before your main sql extraction check this table for the needed data (the query should be really fast). If nothing exists, make your "magic" transaction (preferably not using Django's ORM). The result use it for your purpose, but also duplicate it into your secondary table.
  • when you get tired of doing unnecessary operations, kill some sleep hours and optimize your sql structure, based on the projects needs: what data is needed, amount of db writes vs amount of db reads, inner/outer joins and so on ...
StefanNch
  • 2,569
  • 24
  • 31