Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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!

like image 726
Raoot Avatar asked Oct 20 '12 12:10

Raoot


People also ask

Can you group by date in SQL?

To group by date part, use the GROUP BY clause and the EXTRACT() function. Pass EXTRACT() the date parts to isolate.


2 Answers

def grouped_created_at
  @created_ats = @products.group_by { |c| c.created_at.to_date }
end
like image 63
rossta Avatar answered Oct 17 '22 00:10

rossta


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.

like image 32
Matt Avatar answered Oct 17 '22 01:10

Matt