public
Last active

Adds time zone aware data grouping method to ActiveRecord. If you want to group a large set of database rows by date using a timestamp as reference, the optimal way is to do it right in the database. When doing it though there are some issues with time zones that #group_date solves. For example, if a UTC timestamp is set to 2013-12-01 01:00 and you're in Brazil (= 2013-11-30 22:00) you'd want date() to return 2013-11-30 but database will actually return 2013-12-01 as it doesn't know anything about time zones.

  • Download Gist
group_date.rb
Ruby
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
module ActiveRecord
module Querying
delegate :group_date, :to => :scoped
end
 
module QueryMethods
def group_date(column_name = nil)
return self if column_name.blank?
 
# Rails uses a non standard time zone naming.
# Let's get tz database standard which is used by many databases.
# Using this instead of time offset or other approaches is necessary
# in order to deal with daylight saving time automatically.
zone = Time.zone.tzinfo.name
 
# Rails stores timestamps without time zones by default.
# So we must first set a zone to column_name (column_name at time zone 'UTC').
# Note that even though Rails does that under the hood when you use ActiveRecord,
# that's not applicable here.
# Then, we set column to application's time zone so that the database can
# convert timestamp to proper date.
convert = "at time zone 'UTC' at time zone '#{zone}'"
date = "date(#{table.name}.#{column_name} #{convert})"
 
# Since converting date is not that straight-forward,
# this will automatically return converted date.
# We gotta convert output date to assure Rails won't convert it again.
clone.group(date).select("#{date} #{convert} as #{column_name}")
end
end
end

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.