title: Rails : How to build statistics per day/month/year or How database agnostic SQL functions are missing (ex. : STRFTIME, DATE_FORMAT, DATE_TRUNC)
body: <p>I have been searching all over the web and I have no clue.</p>
  
  <ul>
  <li>Suppose you have to build a dashboard in the admin area of your Rails app and you want to have the <strong>number of subscriptions per day</strong>.</li>
  <li>Suppose that you are using <strong>SQLite3 for development</strong>, <strong>MySQL for production</strong> (pretty standard setup)</li>
  </ul>
  
  <p>Basically, there are two options :</p>
  
  <p>1) <strong>Retrieve all rows from the database</strong> using <code>Subscriber.all</code> and aggregate by day in the Rails app using the <code>Enumerable.group_by</code> :</p>
  
  <pre><code>@subscribers = Subscriber.all
  @subscriptions_per_day = @subscribers.group_by { |s| s.created_at.beginning_of_day }
  </code></pre>
  
  <p>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 !</p>
  
  <p>2) <strong>Run a SQL query in the database using aggregate and date functions</strong> :</p>
  
  <pre><code>Subscriber.select('STRFTIME("%Y-%m-%d", created_at) AS day, COUNT(*) AS subscriptions').group('day')
  </code></pre>
  
  <p>Which will run in this SQL query :</p>
  
  <pre><code>SELECT STRFTIME("%Y-%m-%d", created_at) AS day, COUNT(*) AS subscriptions
  FROM subscribers
  GROUP BY day
  </code></pre>
  
  <p>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.</p>
  
  <p>... but wait... now the app has to go live in my production env which uses MySQL !
  Replace <code>STRFTIME()</code> with <code>DATE_FORMAT()</code>.
  What if tomorrow I switch to PostgreSQL ?
  Replace <code>DATE_FORMAT()</code> with <code>DATE_TRUNC()</code>.</p>
  
  <p>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 <strong>translate SQL functions that do the exact same thing, but have different syntax in each RDBMS</strong> (this difference is really stupid, but hey, it's too late to complain about it) ?</p>
  
  <p>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.</p>
  
  <p>Tell me I'm missing something :)</p>
  
  <h1>EDIT</h1>
  
  <p>It's been a few years since I posted this question.
  Experience has shown that I should use the same DB for dev and prod. So I now consider the database agnostic requirement irrelevant.</p>
  
  <p><a href="http://12factor.net/dev-prod-parity" rel="noreferrer">Dev/prod parity</a> FTW.</p>