Inconsistent results when grouping by a date field using time_zone: false

[nicholasmott]

When grouping by a date field and using time_zone: false the groups will show inconsistent results for any time zone that is not in UTC. We have observed this on a change to a new month when using group_by_month.

Easiest to understand with an example.

Time.zone.to_s
=> "(GMT+10:00) Melbourne"

Invoice.group_by_month(:issue_date, time_zone: false, last: 3, format: '%B').sum(:net_amount)
=> {"July"=>100, "August"=>300, "September"=>500}

require 'active_support/testing/time_helpers'

class TimeHelp
  include ActiveSupport::Testing::TimeHelpers
end

TimeHelp.new.travel_to Time.zone.local(2023,9,01,9,0,0)

Time.zone.now
=> Fri, 01 Sep 2023 09:00:00.000000000 AEST +10:00
Time.zone.now.utc
=> 2023-08-31 23:00:00 UTC

Invoice.group_by_month(:issue_date, time_zone: false, last: 3, format: '%B').sum(:net_amount)
=> {"June"=>400, "July"=>100, "August"=>300}

Even though it is the 1st September in Melbourne because the equivalent UTC time is still the 31st August the grouping is incorrect by one month.

If the time zone was the other direction, eg US Pacific, then September would show up in the results while the actual date is still the 31st August.

Removing the time_zone: false option will display the correct grouping but it changes the where condition so that the sum may no longer be accurate.

Eg:

# With time_zone: false
WHERE ("invoices"."issue_date" >= '2023-07-01 00:00:00' AND "invoices"."issue_date" < '2023-10-01 00:00:00')

# Without time_zone: false
WHERE ("invoices"."issue_date" >= '2023-06-30 14:00:00' AND "invoices"."issue_date" < '2023-09-30 14:00:00')