Output array based on date (sales figures by day)

Question

I have a table with a float called 'cost' and timestamp called'created_at'.

I would like it to output an array with the summing the costs for each particular day in the last month.

Something like:

    @newarray = []  #creating the new array
    month = Date.today.month # Current Month
    year = Date.today.year # Current Year
    counter = 1 # First Day of month

      31.times do #for each day of the month (max 31)        
          #adding sales figures for that day
          @newarray.push(Order.sum(:cost, :conditions => {:created_at => "#{year}-#{month}-#{counter}"}))         
          counter = counter + 1 #go onto next day
      end

However this doesn't work as all the timestamps have a time as well.

Apologies in advance for the poor title, I can't seem to think of a sensible one.

Answer 1

You should be able to use code like the following:

sales_by_day = Order.sum(:cost, 
                         :group => 'DATE(created_at)', 
                         :conditions => ['DATE(created_at) > ?', 31.days.ago])
(0..30).collect { |d| sales_by_day[d.days.ago.to_date.to_s] || 0 }.reverse

This will sum the order cost by day, then create an array with index 0 being the last 24 hours. It will also take only one query, whereas your example would take 31.

To change the cut off date, replace 31.days.ago with an instance of the Time class. Documentation here: http://ruby-doc.org/core/classes/Time.html

Good luck!

Answer 2

This should work:

(Date.today.beginning_of_month..Date.today.end_of_month).map { |d|
  Order.sum(:cost, :conditions => ['created_at >= ? AND created_at < ?', d, d+1])
}

Although I think you should try getting it using a single query, instead of making one for each day.