#18969 closed Bug (duplicate)

ORM filtering with "year" lookup for dates

Reported by: saippuakauppias Owned by: nobody
Component: Database layer (models, ORM) Version: master
Severity: Normal Keywords:
Cc: dirigeant, tomas.ehrlich@… Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no


When I do query with lookup in orm:

Document.objects.filter(given_on__year=1).values_list('given_on', flat=True)


Document.objects.filter(given_on__year='0001').values_list('given_on', flat=True)

I get results:

[, 8, 31),, 8, 31),, 8, 29), '...(remaining elements truncated)...']

But I would expect dates in interval 0001-01-01 ... 0001-12-31 (YYYY-MM-DD).

Generated SQL query:

print Document.objects.filter(given_on__year='0001').values_list('given_on').query
SELECT "documents_document"."given_on" 
FROM "documents_document" 
WHERE "documents_document"."given_on" BETWEEN 1-01-01 00:00:00 and 1-12-31 23:59:59.999999 
ORDER BY "documents_document"."created_on" DESC

In django not possible give results with first year.

PS: question in stackoverflow
PPS: tested in Django 1.3.2 version

Attachments (2)

18969.patch (1.5 KB) - added by dirigeant 21 months ago.
18969-1.patch (2.2 KB) - added by Elvard 21 months ago.
Tests added

Download all attachments as: .zip

Change History (9)

Changed 21 months ago by dirigeant

comment:1 Changed 21 months ago by dirigeant

  • Cc dirigeant added
  • Has patch set
  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

Attached patch fixes this issue for all common db backends

SELECT "auth_user"."last_login" FROM "auth_user" WHERE "auth_user"."last_login" BETWEEN 0001-01-01 and 0001-12-31 23:59:59.999999

comment:2 Changed 21 months ago by ptone

  • Component changed from ORM aggregation to Database layer (models, ORM)
  • Needs tests set
  • Triage Stage changed from Unreviewed to Accepted
  • Version changed from 1.3 to master

Changed 21 months ago by Elvard

Tests added

comment:3 Changed 21 months ago by Elvard

  • Cc tomas.ehrlich@… added
  • Needs tests unset

I'm just unsure if wrote the test right. I've added new fixture (for date 0001-01-01) at the bottom of existing test (test_date_lookup) and related assertion, so I don't have to rewrite any previous assertions… Usually are fixtures at the top of the test. Does it matter?

comment:4 Changed 17 months ago by fhahn

  • Cc flo@… added

This issue seems fixed already, your test case works for me on sqlite and postgres using the current master, the sql is generated correctly.

I'm not sure about the position of the test date generation, but you could create an article with datetime.datetime(1, 3, 3) and it wouldn't mess with the previous assertions.

Could you upload a patch with the test case only? Or even better, create a pull request on github?

This ticket describes a bug similar to #18176

comment:5 Changed 17 months ago by fhahn

  • Cc flo@… removed

I've created a pull request with your test and updates to the queryset documentation (the __year, __month, .. lookups accept integers and strings), see #18176:

comment:6 Changed 17 months ago by aaugustin

If the current implementation happens to accept strings, fantastic. But I'm against documenting it — because it means committing to support strings in these lookups forever.

Years, months, and friends are fundamentally integers and "there should be one-- and preferably only one --obvious way to do it."

comment:7 Changed 17 months ago by aaugustin

  • Resolution set to duplicate
  • Status changed from new to closed

Closing as a duplicate of earlier ticket #18176.

Add Comment

Modify Ticket

Change Properties
<Author field>
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'

E-mail address and user name can be saved in the Preferences.

Note: See TracTickets for help on using tickets.