CODEWITHSUNDEEP
ruby-on-rails-3activerecord
Raoot
Raoot

Reputation: 1771

Grouping created_at by date only?

I have the following in my Product model.

def grouped_created_at
  @created_ats = @products.group_by { |c| c.created_at }
end

Works fine, but I want to group by the date only. As this is a timestamp, i'm not getting the results I need. Can anyone advise how to modify this?

Many thanks!

Upvotes: 9

Views: 8602

Answers (3)

Matt
Matt

Reputation: 6330

Read this if you want to group records by your CLIENT'S timezone!

I've spent about a day on this, so I hope this post helps someone. I was trying to count my users activities for each of the last 10 days. Easy enough right? This is what I tried that didn't work:

Not working

Activity.select("user_id", "id", "created_at")
  .where(user_id: @user.id)
  .where("created_at >= ?", DateTime.now.beginning_of_day - 10.days) 
  .group("user_id", "DATE(created_at)") # <- POSTGRES' DATE FUNCTION
  .count("id")

The problem above is that this query would group the activities by UTC time. For example, In Toronto (UTC - 4 hours) if I create an activity after 8PM at night on April 1st, Postgres would think it was created the next day (April 2nd) instead of "today" (April 1st). This was no good, since I wanted to group these activities by the user's timezone.

Using the handy browser_timezone_rails gem (that uses javascript to attach user's timezones to requests), and switching my .group line to this was what ultimately worked:

Working

.group("user_id", "DATE(created_at AT TIME ZONE 'UTC' AT TIME ZONE '#{Time.zone.name}')")

You'll see there were actually two AT TIME ZONEs required. The first confirms that our timestamp is in UTC, and the second converts the timestamp to the Time.zone set by the gem (for example: 'America/New_York').

Hopefully this helps someone. This answer on the postgres group was super help for me, as well.

Upvotes: 3

imsinu9
imsinu9

Reputation: 769

FOR MY FELLAS WHO LAND ON THIS PAGE - ALTERNATE SOLUTION

BASED ON WHAT DATABASE YOU USE

For MySql

Model.group("date(table_name.created_at)")

For SQLite

Model.group("strftime('%Y-%m-%d', table_name.created_at)")

PostgreSQL:

Model.group("table_name.created_at::date")

WILL RESULT YOU

ActiveSupport::OrderedHash like

{Sun, 13 Sep 2015=>2,
 Fri, 11 Sep 2015=>1,
 Tue, 01 Sep 2015=>1,
 Mon, 31 Aug 2015=>2,
 Wed, 26 Aug 2015=>1,
 Tue, 11 Aug 2015=>2,
 Sun, 09 Aug 2015=>2,
 Thu, 30 Jul 2015=>3,
 Tue, 07 Jul 2015=>3,
 Mon, 06 Jul 2015=>1,
 Sun, 05 Jul 2015=>2,
 Thu, 02 Jul 2015=>5}

Upvotes: -3

rossta
rossta

Reputation: 11494

def grouped_created_at
  @created_ats = @products.group_by { |c| c.created_at.to_date }
end

Upvotes: 16

Related Questions