Geek Answers Handbook

How to filter by time in a date field?

The datetime field is "entered" in the model. I want to query the data to find all the records that were made between noon (start_time) and 17:00 (end_time).

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

(as I expected), I get an error message:

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

So, I know that 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 message:

"FieldError: Join on field 'start' not permitted. Did you misspell 'time' for the lookup type?"
+5
source share
7 answers

Instead, you can filter in python using db 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's less ugly for you:
  • processes a lot of data in a for loop,
  • or use .extra (..) and bypassing the orm-system
+2
source

, , extra where-clause (: DB- ).

, Postgres - :

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

12 17, , params extra, , sql %s .

+5

SQLite , :

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

datetime start_time end_time?

:

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>]
0

x<=arg x>=arg2.

0

- , - :

, ():

class Availability(...):
    start = models.DateTimeField()
    end = models.DateTimeField()

class Constraint(...):
    from = models.TimeField()
    to = models.TimeField()

, .

Postgres :

Availability.objects.extra(
    where=['NOT (start::time, "end"::time) OVERLAPS (\'{0}\'::time, \'{1}\'::time)'.format(
        from.strftime("%H:%M"), to.strftime("%H:%M")
    )]
)

, DateTime Time (, , timestamp time without timezone Postgres) ::time, OVERLAPS .

:

Availability.objects.extra(
    where=['start::time BETWEEN \'{0}\'::time AND \'{1}\'::time AND
            "end"::time BETWEEN \'{0}\'::time AND \'{1}\'::time'.format(
        from.strftime("%H:%M"), to.strftime("%H:%M")
    )]
)
0

. , , SQL , Django.

fromRange=datetime.datetime.strptime(request.GET.get('from'),'%Y-%m-%dT%H:%M:%S.%fZ')
toRange=datetime.datetime.strptime(request.GET.get('to'),'%Y-%m-%dT%H:%M:%S.%fZ')

entry = Entry.objects.filter(entryTime__range=(fromRange,toRange))
-2

More articles:


All Articles