tags:

views:

569

answers:

4

In the model 'entered' is a datetime field. I want to query the data to find all entry's that where made between noon(start_time) and 5:00pm (end_time).

selected = Entry.objects.filter(entered__gte=start_time, entered__lte=end_time)

(as I expected)I get an error of:

"ValidationError: Enter a valid date/time in YYYY-MM-DD HH:MM[:ss[.uuuuuu]] format."

So I know I can use __year so I tried.

selected = Entry.objects.filter(entered__time__gte=start_time, entered__time__lte=end_time)

I get an error of:

"FieldError: Join on field 'start' not permitted. Did you misspell 'time' for the lookup type?"
+3  A: 

I don't believe there's built-in support for this, but you can pass extra where-clause parameters (warning: some possibility of introducing DB-dependent behaviour here).

For example, on Postgres, something like:

Entry.objects.extra(where=['EXTRACT(hour from entered) >= 12 and '\
                    'EXTRACT(hour from entered) < 17'])

If you're using potentially unsafe input to determine the values 12 and 17, note that you can also specify a params option to extra that will ensure proper quoting and escaping, and then use the standard sql %s placeholders in your where statement.

Jarret Hardie
I was just about to post something along those lines. Using SQLite, `Entry.objects.extra(where=["time(entered) between '12:00' and '17:00'"])` should do the same.
Arnaud
Of course you can ask for time ranges without custom sql. see my answer
vikingosegundo
A: 

Did you provide datetime objects for start_time and end_time?

A quick try-out:

class Entry(models.Model):
    entered = models.DateTimeField()

>>> from datetime import datetime
>>> Entry(entered = datetime.now()).save()
>>> Entry.objects.filter(entered__lte = datetime.now())
[<Entry: Entry object>]
>>> Entry.objects.filter(entered__gte = datetime.now())
[]
>>> Entry.objects.filter(entered__gte = datetime.now(), entered__lte=datetime(2009,11,1,0,0))
[<Entry: Entry object>]
vikingosegundo
Yes, but you don’t really answer the question: it’s supposed to return every entry made between 12pm and 7pm, not matter what date.
Arnaud
And I do not want to filter only by the hour the time could be 12:34:54 to 14:23:06, the important thing is that the filter only consider the time and not the date.
Steven H.
I’m afraid that using the `extra()` modifier is the only option, then. Steven, what DB backend are you using?
Arnaud
Oh, yes. I misread the question
vikingosegundo
+1  A: 

Using SQLite as an example, a relatively clean and generic solution would be:

Entry.objects.extra(where=["time(entered) between '%s' and '%s'"],
                    params=[start_time.strftime("%H:%M"), end_time.strftime("%H:%M")])
Arnaud
+2  A: 

You could filter in python instead using the db's mechanisms:

for e in Entry.objects.all():
   if i.entered.hour>= 9 and i.entered.hour < 17 :# or break down to minutes/seconds
        list.append(e)

but both solutions are ugly, i think.

Steve, you have to decide, what is less ugly for you:

  • processsing a lot of data in a for-loop,
  • or use .extra(..) and by-passing the orm-system
vikingosegundo
As of this moment this is the option I prefer. I still would like a better option.
Steven H.