I have this query for MySQL database:
Article.where('DAY( created_at ) = DAY( ? )', day)
And I try to use the query above also in the PostgreSQL database, specifically I am trying something like this:
Article.where("DATE_PART('day', created_at) = DATE_PART('day', ?)", today)
But I am getting the error PGError: ERROR: function date_part(unknown, unknown) is not unique
Why is there that error? I thought I have the syntax by documentation...
It seems
today
is a string of the patternYYYY-MM-DD
. You could just extract the rightmost two characters, instead of casting to date and then extracting a number. Would be faster and simpler:right()
requires PostgreSQL 9.1 or later. In older version you can subsitute:Because you want characters 9 and 10.
This should work, too:
Note, that you must cast
'2012-01-16'
todate
, not tointerval
.'2012-01-16'::interval
would be nonsense.