Rails : How to build statistics per day/month/year or How database agnostic SQL functions are missing (ex. : STRFTIME, DATE_FORMAT, DATE_TRUNC)

Question

Hi everyone,

I have been searching all over the web and I have no clue.

• Suppose you have to build a dashboard in the admin area of your Rails app and you want to have the number of subscriptions per day.
• Suppose that you are using SQLite3 for development, MySQL for production (pretty standard setup)

Basically, there are two options :

1) Retrieve all rows from the database using Subscriber.all and aggregate by day in the Rails app using the Enumerable.group_by :

@subscribers = Subscriber.all
@subscriptions_per_day = @subscribers.group_by { |s| s.created_at.beginning_of_day }

I think this is a really bad idea. Retrieving all rows from the database can be acceptable for a small application, but it will not scale at all. Database aggregate and date functions to the rescue !

2) Run a SQL query in the database using aggregate and date functions :

Subscriber.select('STRFTIME("%Y-%m-%d", created_at) AS day, COUNT(*) AS subscriptions').group('day')

Which will run in this SQL query :

SELECT STRFTIME("%Y-%m-%d", created_at) AS day, COUNT(*) AS subscriptions
FROM subscribers
GROUP BY day

Much better. Now aggregates are done in the database which is optimized for this kind of task, and only one row per day is returned from the database to the Rails app.

... but wait... now the app has to go live in my production env which uses MySQL ! Replace STRFTIME() with DATE_FORMAT(). What if tomorrow I switch to PostgreSQL ? Replace DATE_FORMAT() with DATE_TRUNC().

I like to develop with SQLite. Simple and easy. I also like the idea that Rails is database agnostic. But why Rails doesn't provide a way to translate SQL functions that do the exact same thing, but have different syntax in each RDBMS (this difference is really stupid, but hey, it's too late to complain about it) ?

I can't believe that I find so few answers on the Web for such a basic feature of a Rails app : count the subscriptions per day, month or year.

Tell me I'm missing something :)

Answer 1

If db agnosticism is what you're after, I can think of a couple of options:

Create a new field (we'll call it day_str) for the Subscriber that stores either the formatted date or a timestamp and use ActiveRecord.count:

daily_subscriber_counts = Subscriber.count(:group => "day_str")

The trade-off is of course a slightly larger record size, but this would all but eliminate performance worries.

You could also, depending on how granular the data that's being visualized is, just call .count several times with the date set as desired...

((Date.today - 7)..Date.today).each |d|
    daily_subscriber_counts[d] = Subscriber.count(:conditions => ["created_at >= ? AND created_at < ?", d.to_time, (d+1).to_time)
end

This could also be customized to account for varying granularities (per month, per year, per day, per hour). It's not the most efficient solution in the case that you wanted to group by day on all of your subscribers (haven't had a chance to run it either), but I would imagine you'd want to group by month, day, hour if you're viewing the a years worth, months worth or days worth of data respectively.

If you're willing to commit to mysql and sqlite you could use...

daily_subscriber_counts = Subscriber.count(:group => "date(created_at)")

...as they share similar date() functions.