Defect #32532

SQL to get count does not work correctly when grouping issues using created_on when setting config.time_zone

Added by Mizuki ISHIKAWA 3 months ago. Updated 3 months ago.

Status:NewStart date:
Priority:NormalDue date:
Assignee:-% Done:

0%

Category:Issues filter
Target version:-
Resolution: Affected version:

Description

  • config.time_zone = 'Tokyo' # Other than UTC
  • User time zone is nil
    In the case of the above settings, the count when grouped with created_on may not be displayed correctly.

created_on of grouping target issues:

[Thu, 20 Jul 2006 04:04:21 JST +09:00,
 Thu, 20 Jul 2006 04:07:27 JST +09:00,
 Thu, 14 Nov 2019 16:27:41 JST +09:00,
 Tue, 19 Nov 2019 16:27:41 JST +09:00,
 Sun, 24 Nov 2019 16:27:41 JST +09:00,
 Sun, 24 Nov 2019 16:27:41 JST +09:00,
 Sun, 24 Nov 2019 16:27:41 JST +09:00,
 Tue, 26 Nov 2019 16:27:41 JST +09:00,
 Fri, 29 Nov 2019 16:26:41 JST +09:00,
 Fri, 29 Nov 2019 16:26:41 JST +09:00,
 Fri, 29 Nov 2019 16:26:41 JST +09:00]

Expected aggregation results(Date in Tokyo time zone):

> Issue.all.open.group_by{|i| i.created_on.to_date}.map{|k, values| [k, values.count]}.to_h
{Thu, 20 Jul 2006=>2,
 Tue, 19 Nov 2019=>1,
 Tue, 26 Nov 2019=>1,
 Fri, 29 Nov 2019=>3,
 Thu, 14 Nov 2019=>1,
 Sun, 24 Nov 2019=>3}

Actual aggregation results(Date in UTC time zone):

> query.result_count_by_group
{Wed, 19 Jul 2006=>2,
 Tue, 19 Nov 2019=>1,
 Tue, 26 Nov 2019=>1,
 Fri, 29 Nov 2019=>3,
 Thu, 14 Nov 2019=>1,
 Sun, 24 Nov 2019=>3}


# http://www.redmine.org/projects/redmine/repository/entry/trunk/lib/redmine/database.rb#L71
        if postgresql?
          if time_zone
            identifier = ActiveSupport::TimeZone.find_tzinfo(time_zone.name).identifier
            "(#{column}::timestamptz AT TIME ZONE '#{identifier}')::date" 
          else
            "#{column}::date" 
          end
        else
         # ...

The code above is executed to get the key date for the group.
Instead of returning "#{column}::date" when the user time zone is nil and config.time_zone is set, it should return the date converted to the time zone set in config.time_zone.

This issue is related to #13803.

screenshot.png (206 KB) Mizuki ISHIKAWA, 2019-11-29 08:54

fix-32532.patch Magnifier (2.12 KB) Mizuki ISHIKAWA, 2019-11-29 09:07

History

#1 Updated by Mizuki ISHIKAWA 3 months ago

I have attached a patch that fixes the problem.
I'm not very familiar with SQL, so I'd like someone to check if the change is correct.

Also available in: Atom PDF