DateField __year queries don't return all records.

[dougvanhorn@…]

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.

[Doug Van Horn]

Patches the unit test to expose the problem.

[Doug Van Horn]

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

[James Bennett]

Duplicate of #3689.

[Karen Tracey <kmtracey@…>]

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.

[anonymous]

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...

[Doug Van Horn]

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.