Rails - Group by date using same attribute name returns different answers

Question

date_start = Time.parse('11/08/2015').beginning_of_day
date_end = Time.parse('11/08/2015').end_of_day

created_at_day_tz = "date(created_at AT TIME ZONE \'UTC\'
                     AT TIME ZONE \'#{Time.zone.tzinfo.identifier}\')"

users = User.where("users.created_at BETWEEN ? AND ?", date_start, date_end)

Grouping by created_at as created_at_day (date only, new name for the groupped attribute)

grouped_with_timezone_day = users.group(created_at_day_tz).
  order(created_at_day_tz).
  select("#{created_at_day_tz} as created_at_day, count(*) as count")
# grouped_with_timezone_day.map {|u| [u.created_at_day, u.count] }
# => [[Tue, 11 Aug 2015, 186]]

Grouping by created_at as created_at (date only, same name for the groupped attribute)

grouped_with_timezone = users.group(created_at_day_tz).
  order(created_at_day_tz).
  select("#{created_at_day_tz} as created_at, count(*) as count")
# grouped_with_timezone.map {|u| [u.created_at, u.count] }
# => [[Mon, 10 Aug 2015 21:00:00 BRT -03:00, 186]]

Why the results differ if the records are the same? Why one result comes with timezone, as DateTime, and the other comes as Date only?

Is activerecord 'casting' to DateTime with Timezone because created_at is defined that way (btw, this makes the dates incorrect in this case)?

Answer 1

The timestamp isn't incorrect - that is, it's 2015-08-11 at midnight UTC - it's just displaying in your local time.

Rails has a bit of special behavior for created_at and updated_at:

The timestamps macro adds two columns, created_at and updated_at. These special columns are automatically managed by Active Record if they exist.

It always treats created_at coming back from a query as a timestamp. Your query returns just the date 2015-08-11, which is interpreted as midnight. When printed, the timestamp is displayed in your locale's timezone (which I presume must be -03:00), leading to 3 hours before midnight on the 11th.

When you name the result created_at_day, you avoid Rails converting it to a timestamp and get just the date you expect.