Defect #7218: Date range error on issue query - Redmine

Actions

Copy link

Defect #7218

closed

Date range error on issue query

Added by Felix Schäfer almost 14 years ago. Updated over 13 years ago.

Status:

Closed

Priority:

Normal

Assignee:

Category:

Issues

Target version:

1.2.0

Start date:

2011-01-03

Due date:

% Done:

Estimated time:

Resolution:

Fixed

Affected version:

Description

When filtering issues for "this week" on issues the ranges are broken on core as well as on custom fields. The following examples were all tested against r4471 on MySQL with german locale (week starts on monday), the tests were realized on a sqlite installation too yielding similar but sometimes even "more broken" results.

When filtering issues for "this week" on core issue field, for example "Due date", the query returns results for 8 days instead of 7. This query executed today returns results from 03.01.2011 to 10.01.2011 (both dates included), whereas the current week is from 03.01.2011 to 09.01.2011.

When filtering issues for "this week" on a custom issue field with the type date, the query doesn't return results for the first day of the expected range (in this case monday) but does return them for one extra day at the end of the range. This query executed today returns results from 04.01.2011 to 10.01.2011 (both dates included), whereas the current week is from 03.01.2011 to 09.01.2011.

Actions

Copy link

Updated by Felix Schäfer almost 14 years ago

The culprit is source:/trunk/app/models/query.rb#L606, though I'm not sure where the problem is (I'd wager it has something to do with querying a date column with time values), but using the same "trick"/hack as in r2054, I was able to produce following diff:

diff --git a/app/models/query.rb b/app/models/query.rb
index 7a42de1..b1b919d 100644
--- a/app/models/query.rb
+++ b/app/models/query.rb
@@ -602,7 +602,7 @@ class Query < ActiveRecord::Base
       ((Date.today.cwday == 7) ? Time.now.at_beginning_of_day : Time.now.at_beginning_of_week - 1.day) :
         # week starts on monday (Rails default)
         Time.now.at_beginning_of_week
-      sql = "#{db_table}.#{db_field} BETWEEN '%s' AND '%s'" % [connection.quoted_date(from), connection.quoted_date(from + 7.days)]
+      sql = "#{db_table}.#{db_field} BETWEEN '%s' AND '%s'" % [connection.quoted_date((from - 1.day).to_time.end_of_day), connection.quoted_date((from + 6.days).to_time.end_of_day)]
     when "l" 
       from = l(:general_first_day_of_week) == '7' ?
       # week starts on sunday

This seems to alleviate the problem for me on MySQL and as far as I tested it on SQLite too. I'd really like this operator tested though (currently the only test done is if it runs, not if the result is correct), but I haven't come up with a good way to do it yet.

Actions

Copy link