7

Does anyone know how I can sort (in this instance date) my django query set against todays date ?

class Person(models.Model):
    name = models.CharField(max_length=50)
    date = models.DateField()

My goal is to list the name and date entries. At the top of the list will be the entry with the date that is closest to todays date (day/month).

felix001
  • 15,341
  • 32
  • 94
  • 121

2 Answers2

8

You can use extra queryset method to select additional data from database table.

This is example that works with MySql:

Person.objects.extra(select={
    'datediff': 'ABS(DATEDIFF(date, NOW()))'}).order_by('datediff')

DATEDIFF - returns difference in days bewteen two dates, ABS - returns absolute value. For sqlite, there is different syntax, see this answer.

EDIT: use current year

Person.objects.extra(select={
    'datediff': "ABS(DATEDIFF(CONCAT(YEAR(now()), '-', MONTH(date), '-', DAY(date)), NOW()))"}
).order_by('datediff')

EDIT 2: optimized *

from datetime import date
dayofyear = int(date.today().strftime("%j"))

datediff = 'LEAST(ABS(DAYOFYEAR(date) - %d), ABS((366 - %d + DAYOFYEAR(date))) MOD 366)' % (
      dayofyear, dayofyear
    )
Person.objects.extra(select={'datediff': datediff}).order_by('datediff')

EDIT 3: closest date after given (todays) date

    from datetime import date
    dayofyear = int(date.today().strftime("%j"))

    datediff = '(DAYOFYEAR(date) - %d + 365) MOD 365' % (
          dayofyear
        )
    Persion.objects.extra(select={'datediff': datediff}).order_by('datediff')
Community
  • 1
  • 1
bmihelac
  • 6,233
  • 34
  • 45
  • Thanks, I think this would work if I could perform this query but without the year i.e the closest day/month to the current date. – felix001 Apr 14 '13 at 17:01
  • What do you mean with "but without the year" - do you mean that 04-15-1999 would come before 04-01-2013 (with assumption that current date is 04-15-2013.) – bmihelac Apr 15 '13 at 07:01
  • So if you have 12-4-1980 and 1-4-1991 and the date was 9-4-2013 then 12-4-1980 would come first. i.e I only want to sort the dates using the month and day as each will be used as a date of birthday. So I want to see the closest birthday. – felix001 Apr 15 '13 at 10:03
  • I have updated the answer to use current year. This could be optimised. – bmihelac Apr 15 '13 at 10:23
  • "Edit 2" version compares days of year and not dates. – bmihelac Apr 15 '13 at 11:41
  • Ive tried the EDIT1 option but I get : unsupported operand type(s) for -: 'str' and 'str' – felix001 Apr 16 '13 at 15:04
  • Thanks it kind of works but now against todays date the 15th April comes before June 6th. Where I want show the next closet date, i.e the next upand coming birthday. – felix001 Apr 16 '13 at 20:57
  • Do you want to show sort by closest date after given date? solution above sorts by closest date, bot before and after given day/month. – bmihelac Apr 17 '13 at 05:42
  • Yep that's correct, I want to show sort by closest date after given (todays) date, Thanks again. – felix001 Apr 17 '13 at 10:24
  • Added EDIT 3: closest date after given (todays) date - this should do exactly what you want. – bmihelac Apr 18 '13 at 07:33
  • Many Thanks, just want I was after :o) – felix001 Apr 18 '13 at 20:08
-1

If you want to sort based on date, you can order as: .order_by('date') on a result queryset.

I'm not sure if that answers your question. In case you mean you want to select only the Persons with date of today, you can use:

import datetime
now = datetime.datetime.now()
persons_with_date_today = Person.objects.filter(date=now)
karthikr
  • 97,368
  • 26
  • 197
  • 188
Javaaaa
  • 3,788
  • 7
  • 43
  • 54
  • This is filtering the results, the OP needs to sort it based on today's date – karthikr Apr 09 '13 at 20:32
  • I know, that's what I also answered - but because of ambiguity in the wording of OP I also added explanation in case he needed filtering. – Javaaaa Apr 09 '13 at 20:35
  • The question is, sort in increasing order of difference in timestamp relative to now – karthikr Apr 09 '13 at 20:36
  • example `[today, 2 days ago, 3 days in future, 4 days ago, ...]` – karthikr Apr 09 '13 at 20:37
  • Just a note... that is not timezone-aware so even if you have `USE_TZ` set, it won't use it. Django's `django.utils.timezone` has a `now` method that IS timezone aware so depending on your needs, `timezone.now()` may be better. – Ngenator Apr 09 '13 at 20:58
  • Ive looked at all the options and tested but in all cases either the entry was filtered based on todays date or the entries were sorted but date rather then show the most current (against todays date) at the top. – felix001 Apr 10 '13 at 12:47