rails group records by dates of created_at

Question

So I have a model that I want to retrieve records and group them by dates of the created_at field. But created_at is a datetime field, and I am only interested in the date part. So I am looking for something like a 2D array, first layer would a hash with date string as the key, second layer would be arrays with records. How should I do it?

{
"9/28/2012" => [record, record, record],
"9/29/2012" => [record, record, record],
"9/30/2012" => [record, record, record]
}

At top of the above, how should I do it if I want the above arrange be applied to all records retrieved from this model?

Answer 1

ActiveRecord group method will do what you need. In your case, the problem will be with using created_at which is a datetime, and your constraint to group by date. Since casting date-times to dates is database-specific, the code will need to be database-specific as well.

For MySql you can do:

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

For SQLite you can do:

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

And for PostgreSQL:

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

This code will unfortunately not be portable, but that may not matter to you. If it does, you can always build a wrapper method that selects the correct conversion syntax based on your DBMS.

Answer 2

On ActiveRecord you can do this:

2.6.4 :036 > User.group('created_at::date').count

   (5.3ms)  SELECT COUNT(*) AS count_all, created_at::date AS created_at_date FROM "users" GROUP BY created_at::date
 => {Sat, 27 Feb 2021=>5865