Django

Code

Ticket #6141 (closed: duplicate)

Opened 11 months ago

Last modified 11 months ago

DateField __year queries don't return all records.

Reported by: dougvanhorn@gmail.com Assigned to: nobody
Milestone: Component: Database layer (models, ORM)
Version: SVN Keywords: DateField __year query
Cc: Triage Stage: Unreviewed
Has patch: 1 Needs documentation: 0
Needs tests: 0 Patch needs improvement: 0

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

test.diff (1.1 kB) - added by Doug Van Horn on 12/05/07 21:52:05.
Patches the unit test to expose the problem.
fix.diff (1.9 kB) - added by Doug Van Horn on 12/05/07 21:53:47.
Modifies query.py and the fields module (init.py) to fix the bug.

Change History

12/05/07 21:52:05 changed by Doug Van Horn

  • attachment test.diff added.

Patches the unit test to expose the problem.

12/05/07 21:53:47 changed by Doug Van Horn

  • attachment fix.diff added.

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

12/05/07 22:17:59 changed by ubernostrum

  • status changed from new to closed.
  • resolution set to duplicate.

Duplicate of #3689.

(follow-up: ↓ 3 ) 12/05/07 22:25:14 changed by 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.

(in reply to: ↑ 2 ) 12/06/07 09:00:42 changed by 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...

12/06/07 09:35:52 changed by 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.


Add/Change #6141 (DateField __year queries don't return all records.)




Change Properties
Action