From eafd585dbe958443cb9d06d217b708cd9f48ea37 Mon Sep 17 00:00:00 2001
From: Jean-Philippe Lang <jp_lang@yahoo.fr>
Date: Sat, 7 May 2016 10:58:49 +0000
Subject: [PATCH] Fixes Query#date_clause timezone handling (#22320).

When querying time fields based on date values these should be interpreted
in the user's time zone, since that's what a user usually expects.

Patch by Jens Kraemer.

git-svn-id: http://svn.redmine.org/redmine/trunk@15381 e93f8b46-1217-0410-a6f0-8f06a7374b81
---
 app/models/query.rb     | 12 +++++++++--
 test/unit/query_test.rb | 44 +++++++++++++++++++++++++++++++++++++++++
 2 files changed, 54 insertions(+), 2 deletions(-)

diff --git a/app/models/query.rb b/app/models/query.rb
index 02e3f6cab..d1315d5de 100644
--- a/app/models/query.rb
+++ b/app/models/query.rb
@@ -971,12 +971,20 @@ class Query < ActiveRecord::Base
     end
   end
 
+  def date_for_user_time_zone(y, m, d)
+    if tz = User.current.time_zone
+      tz.local y, m, d
+    else
+      Time.local y, m, d
+    end
+  end
+
   # Returns a SQL clause for a date or datetime field.
   def date_clause(table, field, from, to, is_custom_filter)
     s = []
     if from
       if from.is_a?(Date)
-        from = Time.local(from.year, from.month, from.day).yesterday.end_of_day
+        from = date_for_user_time_zone(from.year, from.month, from.day).yesterday.end_of_day
       else
         from = from - 1 # second
       end
@@ -987,7 +995,7 @@ class Query < ActiveRecord::Base
     end
     if to
       if to.is_a?(Date)
-        to = Time.local(to.year, to.month, to.day).end_of_day
+        to = date_for_user_time_zone(to.year, to.month, to.day).end_of_day
       end
       if self.class.default_timezone == :utc
         to = to.utc
diff --git a/test/unit/query_test.rb b/test/unit/query_test.rb
index 588f5fafd..334f4e479 100644
--- a/test/unit/query_test.rb
+++ b/test/unit/query_test.rb
@@ -1720,4 +1720,48 @@ class QueryTest < ActiveSupport::TestCase
     c = QueryColumn.new('foo', :caption => lambda {'Foo'})
     assert_equal 'Foo', c.caption
   end
+
+  def test_date_clause_should_respect_user_time_zone_with_local_default
+    @query = IssueQuery.new(:name => '_')
+
+    # user is in Hawaii (-10)
+    User.current = users(:users_001)
+    User.current.pref.update_attribute :time_zone, 'Hawaii'
+
+    # assume timestamps are stored in server local time
+    local_zone = Time.zone
+
+    from = Date.parse '2016-03-20'
+    to = Date.parse '2016-03-22'
+    assert c = @query.send(:date_clause, 'table', 'field', from, to, false)
+
+    # the dates should have been interpreted in the user's time zone and
+    # converted to local time
+    # what we get exactly in the sql depends on the local time zone, therefore
+    # it's computed here.
+    f = User.current.time_zone.local(from.year, from.month, from.day).yesterday.end_of_day.in_time_zone(local_zone)
+    t = User.current.time_zone.local(to.year, to.month, to.day).end_of_day.in_time_zone(local_zone)
+    assert_equal "table.field > '#{Query.connection.quoted_date f}' AND table.field <= '#{Query.connection.quoted_date t}'", c
+  end
+
+  def test_date_clause_should_respect_user_time_zone_with_utc_default
+    @query = IssueQuery.new(:name => '_')
+
+    # user is in Hawaii (-10)
+    User.current = users(:users_001)
+    User.current.pref.update_attribute :time_zone, 'Hawaii'
+
+    # assume timestamps are stored as utc
+    ActiveRecord::Base.default_timezone = :utc
+
+    from = Date.parse '2016-03-20'
+    to = Date.parse '2016-03-22'
+    assert c = @query.send(:date_clause, 'table', 'field', from, to, false)
+    # the dates should have been interpreted in the user's time zone and
+    # converted to utc. March 20 in Hawaii begins at 10am UTC.
+    assert_equal "table.field > '2016-03-20 09:59:59.999999' AND table.field <= '2016-03-23 09:59:59.999999'", c
+  ensure
+    ActiveRecord::Base.default_timezone = :local # restore Redmine default
+  end
+
 end
-- 
2.39.5