Rails 4 : how to use UNIX_TIMESTAMP with group YEAR

Question

I have the following code

Call.group('YEAR(created_at)').count

Which returns

{2013=>81577, 2012=>93323, 2011=>79915, 2010=>59084, 2009=>55561}

How can I use UNIX_TIMESTAMP for the years so that the UNIX_TIMESTAMP value is returned (ex. 1356912000000) instead of the year (ex. 2013) ?

I tried

Call.group('UNIX_TIMESTAMP(YEAR(created_at))').count

But it didn't work.

Thank you.

Answer 1

unix_timestamp() expects a date/datetime argument and returns an int. year() returns an integer, so you're going to be doing an invalid operation. You'd need to do something like

UNIX_TIMESTAMP(CONCAT(YEAR(created_at), '-01-01 00:00:00'))

which would evaluate to

UNIX_TIMESTAMP('2013-01-01 00:00:00')

and give you the timestamp for the beginning of the year.

Answer 2

probably a better way to do this - but it should work

counts = {}
# NOTE: could be off by a few records near the end of the year due to timezones
raw_counts = Call.group('YEAR(created_at)').count

raw_counts.each do |key, value|
  # rebuild the hash key
  # convert date to first day of the year
  # use to_i on the date to get unix timestamp
  counts[Time.zone.parse("#{key}-01-01").to_i] = value
end

counts.inspect

{ 
  1293840000 => 221794, 
  1325376000 => 239135, 
  1356998400 => 293378 
}