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!
To group by date part, use the GROUP BY clause and the EXTRACT() function. Pass EXTRACT() the date parts to isolate.
def grouped_created_at
@created_ats = @products.group_by { |c| c.created_at.to_date }
end
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:
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:
.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 ZONE
s 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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With