Project

General

Profile

Actions

Defect #32046

closed

SQL to get counts when using created_on in MySQL to group issues does not work correctly

Added by Mizuki ISHIKAWA over 4 years ago. Updated over 4 years ago.

Status:
Closed
Priority:
Normal
Assignee:
Category:
Issues filter
Target version:
-
Start date:
Due date:
% Done:

0%

Estimated time:
Resolution:
Fixed
Affected version:

Description

Reproduction procedure
  • Use MySQL
  • Time zone data is loaded(If it is not loaded, time zone conversion will fail.)
  • Change user language from UTC to something else
  • Group by created_on at issues/index

query.result_count_by_group executed in QueriesHelper#grouped_query_results returns the following result.

Actual result:

{2006-07-18 14:00:00 +0000=>2,
 2019-08-13 14:00:00 +0000=>1,
 2019-08-18 14:00:00 +0000=>1,
 2019-08-23 14:00:00 +0000=>2,
 2019-08-25 14:00:00 +0000=>1,
 2019-08-28 14:00:00 +0000=>3}

Expected result:

 {Wed, 19 Jul 2006=>2,
 Tue, 13 Aug 2019=>1,
 Sun, 18 Aug 2019=>1,
 Fri, 23 Aug 2019=>2,
 Sun, 25 Aug 2019=>1,
 Wed, 28 Aug 2019=>3}

The number of records cannot be retrieved properly because the keys format is different than expected.

This issue is related to #13803.


Related issues

Related to Redmine - Feature #13803: Implement grouping issues by date (start, due, creation, update, closing dates)ClosedJean-Philippe Lang

Actions
Actions #1

Updated by Mizuki ISHIKAWA over 4 years ago

You have to make changes like the diff below.

 diff --git a/lib/redmine/database.rb b/lib/redmine/database.rb
index 979363329..0f0876157 100644
--- a/lib/redmine/database.rb
+++ b/lib/redmine/database.rb
@@ -80,7 +80,7 @@ module Redmine
           if time_zone
             user_identifier = ActiveSupport::TimeZone.find_tzinfo(time_zone.name).identifier
             local_identifier = ActiveSupport::TimeZone.find_tzinfo(Time.zone.name).identifier
-            "CONVERT_TZ(DATE(#{column}),'#{local_identifier}', '#{user_identifier}')" 
+            "DATE(CONVERT_TZ(#{column},'#{local_identifier}', '#{user_identifier}'))" 
           else
             "DATE(#{column})" 
           end

Actions #2

Updated by Go MAEDA over 4 years ago

  • Related to Feature #13803: Implement grouping issues by date (start, due, creation, update, closing dates) added
Actions #3

Updated by Go MAEDA over 4 years ago

  • Assignee set to Marius BĂLTEANU
  • Target version set to 4.1.0

Marius, could you review this patch? It updates MySQL support for the date grouping filter option committed in r17745.

Actions #4

Updated by Marius BĂLTEANU over 4 years ago

  • Assignee changed from Marius BĂLTEANU to Go MAEDA

Go MAEDA wrote:

Marius, could you review this patch? It updates MySQL support for the date grouping filter option committed in r17745.

The fix works well, thanks for catching and fixing this issue.

Actions #5

Updated by Go MAEDA over 4 years ago

  • Status changed from New to Closed
  • Target version deleted (4.1.0)
  • Resolution set to Fixed

Committed the fix as a part of #13803. Thank you all for working on fixing the issue.

Actions

Also available in: Atom PDF