Getting error "function week(timestamp without time zone) does not exist"

Question

I was following this example How do I change the records being displayed on the page with buttons on the page (RoR) and managed to get the Daily filter working but when trying to set it up for the Weekly filter I get the error PG::Error: ERROR: function week(timestamp without time zone) does not exist

I figured the WEEK function should work automatically as the DATE function did but that's not the case.

model

def self.popularToday
    reorder('votes desc').find_with_reputation(:votes, :all, { :conditions => ["DATE(microposts.created_at) = DATE(NOW())"]})
end
def self.popularWeekly
    reorder('votes desc').find_with_reputation(:votes, :all, { :conditions => ["WEEK(microposts.created_at) = WEEK(NOW())"]})
end

index.html.erb

<form action="" method="get">
  <fieldset>
     <button type="submit" name="show" value="daily">Today</button>
     <button type="submit" name="show" value="weekly">This week</button>
  </fieldset>
</form>

controller

when "daily"
          Kaminari.paginate_array(Micropost.popularToday).page(params[:page]).per(25)
when "weekly"
          Kaminari.paginate_array(Micropost.popularWeekly).page(params[:page]).per(25)

Answer 1

You need:

"SELECT EXTRACT(WEEK FROM TIMESTAMP  microposts.created_at)"

This will retrieve the week number based on the created at timestamp in postgres.

Documentation: http://www.postgresql.org/docs/9.3/static/functions-datetime.html

Answer 2

The name of the table that has my timestamp without time zone data is called create_date. I used EXTRACT(WEEK FROM create_date) and it worked.