1

I know, I know there's a couple very similar questions to this, but they handle Postgres and MySQL.

I have a model (Event) that tracks page visits, I want to group then count events created per month, and show this using Chart.js. But I don't get how to query SQLite to collect events created at a certain month.

SQLite can't handle EXTRACT(), or something like this Model.where("MONTH(created_at) = ?", 9).

I also tried this Event.where("strftime('%-m', created_at) = ?", 9) suggested by this question, with no luck. It just returns 0 no matter what month I try.

As a hack-y long shot, I even tried adding a month column to Event, filling that with created_at.month, but later gave up on that branch.

I'm very new to Rails development and am shaky on querying, but I've looked at the following questions and still can't piece anything together: How to make a query in Postgres to group records by month they were created? (:created_at datetime column), Selecting by month in PostgreSQL, Getting count of elements by `created_at` by day in a given month, Group records by both month and year in Rails, Use active record to find a record by month and day, ignoring year and time

Community
  • 1
  • 1
Casanova Tapia
  • 37
  • 7

2 Answers2

0

The correct substitution for strftime is %m, not %-m.

Furthermore, strftime returns a zero-padded string:

.where("strftime('%m', created_at) = ?", "09")
CL.
  • 173,858
  • 17
  • 217
  • 259
0

My Bloc.io mentor led me to this: http://railscasts.com/episodes/29-group-by-month

So I can do something like this to return a hash with months as the keys:

months = Event.all.group_by { |t| t.created_at.beginning_of_month }

Casanova Tapia
  • 37
  • 7