Search code examples
mysqlruby-on-railsrubytimestampxslt-grouping

Rails 4 : how to use UNIX_TIMESTAMP with group YEAR

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.

Solution

  • 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.