Project

General

Profile

Actions

Defect #32046

closed

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

Defect #32046: SQL to get counts when using created_on in MySQL to group issues does not work correctly

Added by Mizuki ISHIKAWA about 7 years ago. Updated about 7 years ago.

Status:
Closed
Priority:
Normal
Assignee:
Category:
Issues filter
Target version:
-
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 1 (0 open — 1 closed)

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

Updated by Mizuki ISHIKAWA about 7 years ago Actions #1

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

Updated by Go MAEDA about 7 years ago Actions #2

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

Updated by Go MAEDA about 7 years ago Actions #3

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

Updated by Marius BĂLTEANU about 7 years ago Actions #4

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

Updated by Go MAEDA about 7 years ago Actions #5

  • 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: PDF Atom