Opened 17 years ago

Closed 17 years ago

Last modified 17 years ago

#6141 closed (duplicate)

DateField __year queries don't return all records.

Reported by: dougvanhorn@… Owned by: nobody
Component: Database layer (models, ORM) Version: dev
Severity: Keywords: DateField __year query
Cc: Triage Stage: Unreviewed
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

When querying a DateField using the __year lookup type, the first day of the year, January 1, is not returned.

I can't speak to why the code is the way it is right now, but my patch changes the __year lookups to be the same as the __month and __day lookups. As quick background, __year lookups are handled using 'between' in the where clause, versus an SQL function for __month and __day.

  • test.diff modifies the basic test case to highlight the error.
  • fix.diff fixes the bug.

All tests appear to run correctly against sqlite and postgresql.

Attachments (2)

test.diff (1.1 KB ) - added by Doug Van Horn 17 years ago.
Patches the unit test to expose the problem.
fix.diff (1.9 KB ) - added by Doug Van Horn 17 years ago.
Modifies query.py and the fields module (init.py) to fix the bug.

Download all attachments as: .zip

Change History (6)

by Doug Van Horn, 17 years ago

Attachment: test.diff added

Patches the unit test to expose the problem.

by Doug Van Horn, 17 years ago

Attachment: fix.diff added

Modifies query.py and the fields module (init.py) to fix the bug.

comment:1 by James Bennett, 17 years ago

Resolution: duplicate
Status: newclosed

Duplicate of #3689.

comment:2 by Karen Tracey <kmtracey@…>, 17 years ago

Looks like year used to be handled the way suggested here, but was changed in r4505 to use BETWEEN (the changeset comment mentions this should be more efficient?).

There's also a 3rd ticket (#5223) that notes a problem with __year and sqlite. Sounds like the problem might be dependent on sqlite version? The suggested patches for that ticket change the format of the BETWEEN values to not include the time portion.

You mention running the test on sqlite and postgresql -- did you encounter the problem on postgresql or just on sqlite? I don't see a problem with picking up Jan. 1 events with the current code mysql.

in reply to:  2 comment:3 by anonymous, 17 years ago

Replying to Karen Tracey <kmtracey@gmail.com>:

Looks like year used to be handled the way suggested here, but was changed in r4505 to use BETWEEN (the changeset comment mentions this should be more efficient?).

There's also a 3rd ticket (#5223) that notes a problem with __year and sqlite. Sounds like the problem might be dependent on sqlite version? The suggested patches for that ticket change the format of the BETWEEN values to not include the time portion.

You mention running the test on sqlite and postgresql -- did you encounter the problem on postgresql or just on sqlite? I don't see a problem with picking up Jan. 1 events with the current code mysql.

I ran the tests against both databases /after/ I made the change. I only ran against SQLite before I made the change. Looks like things work as expected with Postgres using between.

It's definitely a problem with SQLite only. I posted a question an SQLite forum:

http://www.nabble.com/Querying-DATE-column-with-date-time-string.-tf4956413.html

Here's the SQL that highlights the problem:

$ sqlite3 date_test
SQLite version 3.4.2
Enter ".help" for instructions
sqlite> create table foo (d date null);
sqlite> insert into foo (d) values ('2008-01-01');
sqlite> select d from foo where d between '2008-01-01' and '2008-01-31';
2008-01-01
sqlite> select d from foo where d between '2008-01-01 00:00:00' and '2008-01-31 23:59:59.999999';
sqlite> .quit

I'm going to ignore the problem for now as I'll be moving off of SQLite as soon as my data model firms up. But I'll keep an eye on that post and report back here as to why it doesn't work, if I find out...

comment:4 by Doug Van Horn, 17 years ago

From the gentlemen on the SQLite forum:

sqlite> create table foo( d date null );
sqlite> insert into foo(d) values( '2008-01-01' );
sqlite> select d, typeof(d) from foo;
2008-01-01|text

See http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions.

So dates are stored as strings in SQLite, and 2008-01-01 is 'less than' 2008-01-01 00:00:00, and so falls outside the bounds of the query. I'm going to head over to #5223 and post this information there as that ticket is still open.

Note: See TracTickets for help on using tickets.
Back to Top