How do I group by day instead of date?

Question

ok so i have

 >> list = Request.find_all_by_artist("someBand")
=> [#<Request id: 1, artist: "someBand", song: "someSong", venue: "Knebworth - Stevenage, United Kingdom", showdate: "2011-07-01", amount: nil, user_id: 2, created_at: "2011-01-01 18:14:08", updated_at: "2011-01-01 18:14:09".............

and then

list.group_by(&:created_at).map {|k,v| [k, v.length]}.sort
=> [[Sat, 01 Jan 2011 18:14:08 UTC +00:00, 10], [Sun, 09 Jan 2011 18:34:19 UTC +00:00, 1], [Sun, 09 Jan 2011 18:38:48 UTC +00:00, 1], [Sun, 09 Jan 2011 18:51:10 UTC +00:00, 1], [Sun, 09 Jan 2011 18:52:30 UTC +00:00, 1], [Thu, 10 Feb 2011 02:22:08 UTC +00:00, 1], [Thu, 10 Feb 2011 20:02:20 UTC +00:00, 1]]

the problem is I have a few Sun, 09 Jan and a couple for the 10th, instead of one like this

this is what i need

=> [[Sat, 01 Jan 2011 18:14:08 UTC +00:00, 10], [Sun, 09 Jan 2011 18:34:19 UTC +00:00, 4], [Thu, 10 Feb 2011 20:02:20 UTC +00:00, 2]]

score 27 · Answer 1 · answered Sep 29 '14 at 14:18

27

I think this is a much more elegant and simple solution

list.group_by{|x| x.created_at.strftime("%Y-%m-%d")}

answered Sep 29 '14 at 14:18

Fellps

395
3
7

1

nice and clean! – Almokhtar Feb 05 '22 at 19:11

score 23 · Accepted Answer · answered Feb 13 '11 at 22:55

23

Time is a quite complex object to group by. Assuming you want to group by the creation date, instead of the full Time, start creating a custom method in your model to return the group criteria.

The method should return the creation date, possibly as string.

def group_by_criteria
  created_at.to_date.to_s(:db)
end

Then, group by that method.

list.group_by(&:group_by_criteria).map {|k,v| [k, v.length]}.sort

answered Feb 13 '11 at 22:55

Simone Carletti

173,507
49
363
364

3

What's wrong with just 'created_at.to_date' without the to_s ? – steenslag Feb 13 '11 at 23:10
Objects by default compares true when they have the same `object_id`, unless the `eql?` method is customized. Grouping by complex objects might be dangerous because objects with the same value might not be considered the same. Two time instances referencing the same date are considered equal, but this is not true for every kind of objects. Sorting by String or Fixnum is more secure. – Simone Carletti Feb 13 '11 at 23:18
Simone: Using to_s wouldn't be anymore "secure". What if I have two different objects with the same string representation? Same problem occurs. – rossisdead Aug 29 '12 at 19:52
I got the same problem, please help me here: http://stackoverflow.com/questions/19763144/undefined-method-group-by-day-rails-3-2 – ben Nov 04 '13 at 10:38
@SimoneCarletti I'm curious what's the `(:db)` doing in `to_s(:db)`? As far as I know `to_s` doesn't take any arguments. Can you clarify please? – Mohamad Mar 18 '15 at 22:31
Rails extends `to_s` to accept formatting for dates. You can get more details in the Rails documentation. – Simone Carletti Mar 19 '15 at 11:24
1

I think this is going to pull all the relevant records and then group in Ruby. That is going to be very slow for a large number of records. It would be much faster to group in the (SQL) DB. – B Seven Apr 23 '15 at 21:46

score 9 · Answer 3 · answered May 17 '14 at 12:09

9

There is a gem for that: groupdate.

Usage (from the docs):

User.group_by_day(:created_at).count
# {
#   2013-04-16 00:00:00 UTC => 50,
#   2013-04-17 00:00:00 UTC => 100,
#   2013-04-18 00:00:00 UTC => 34
# }

answered May 17 '14 at 12:09

laffuste

16,287
8
84
91

score 3 · Answer 4 · answered Jul 12 '13 at 16:15

3

Ipsum's answer is actually good and probably the best:

In Arel:

requests = Arel::Table.new(:requests)
query = requests.project("COUNT(*), CAST(requests.created_at AS DATE) as created_at")
query = query.group("CAST (requests.created_at AS DATE)")
Request.find_by_sql(query.to_sql)

answered Jul 12 '13 at 16:15

you786

3,659
5
48
74

This works great, just remove the space after the second CAST, mysql doesn't like that. – Troy S May 02 '19 at 02:53

score 3 · Answer 5 · answered May 09 '18 at 06:28

3

you can use GROUP BY DATE(created_at) in MySQL

On ruby code you can use like this

list.group('DATE(created_at)').map {|k,v| [k, v.length]}.sort

answered May 09 '18 at 06:28

Ibrahim Saifee

31
3

score 0 · Answer 6 · answered Feb 20 '17 at 19:13

0

Group without extra gems:

def self.group_by_day items
   data = items.group_by{|x| x.created_at.to_date}
   chart_data = {}

   data.each do |a,b|
     chart_data.merge!({a => b.count})
   end

   return chart_data
end

answered Feb 20 '17 at 19:13

Abel

3,989
32
31

score 0 · Answer 7 · answered Mar 30 '23 at 12:17

I recently discovered groupdate gem which seems like a good fit for handling this task.

This code example demonstrates how to use it in practice to solve the problem:

list.group_by_day(:created_at)

The most important part here is that behind the scene it generates SQL, that's much more performant than a plan Ruby code.

How do I group by day instead of date?

7 Answers7

Linked

Related