# Finding distinct months using find\_by\_sql

**URL:** <https://www.ruby-forum.com/t/finding-distinct-months-using-find-by-sql/149340>\
**Category:** Rails\
**Created:** [September 23, 2008, 8:13pm UTC](https://www.ruby-forum.com/t/finding-distinct-months-using-find-by-sql/149340 "2008-09-23T20:13:36Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![ititit](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/i/a5b964/32.png) [@ititit](https://www.ruby-forum.com/u/ititit)\
**Post date:** [September 23, 2008, 8:13pm UTC](https://www.ruby-forum.com/t/finding-distinct-months-using-find-by-sql/149340/1 "2008-09-23T20:13:36Z")

</div>

Hello,  
I’m wondering if someone might be able to offer me a solution to the  
following problem that has so far stumped me.  
I’m trying to get the distinct months (and years) from a date field to  
display as a list in a view. For example, there might be a number of  
records stored with dates in the table ‘headlines’:  
name date  
record1 21-09-2008  
record2 15-09-2008  
record3 03-08-2008  
record4 02-08-2008  
record5 12-07-2008  
etc.

From which I’d like to get all distinct years and months to display in  
a view something like what might be seen under the ‘Archives’ section  
of a blog page, like this:  
September 2008  
August 2008  
July 2008  
etc.

I have tried to set the following sql statement in my controller to  
find the months using find\_by\_sql:  
@headlines\_month = Headline.find\_by\_sql(“SELECT distinct  
MONTHNAME(date) FROM headlines”)

However, this errors out as:

PGError: ERROR: function monthname(date) does not exist at character  
17  
HINT: No function matches the given name and argument types. You may  
need to add explicit type casts.  
: SELECT distinct MONTHNAME(date) FROM headlines

It’s monthname() which is what’s causing it to error out as, if i  
remove monthname() there are no problems.  
I’m not sure if this is a problem of my limited understanding of that  
rails command or an error with my sql statement but I’m out of ideas  
so if anyone might have an idea for an effective solution - or can  
point me in the right direction - I’d very much appreciate it.

Thanks in advance.  
Best  
IRT

---

<div class="post-metadata">

**Author:** ![ititit](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/i/a5b964/32.png) [@ititit](https://www.ruby-forum.com/u/ititit)\
**Post date:** [September 23, 2008, 8:58pm UTC](https://www.ruby-forum.com/t/finding-distinct-months-using-find-by-sql/149340/2 "2008-09-23T20:58:18Z")

</div>

Hi,

it looks like your Postgresql doesn’t support the function monthname().  
Try this statement on a Postgresql-console.  
Which Version of Postgresql do you have installed?

Greetings  
Johannes

Am Dienstag, den 23.09.2008, 10:43 -0700 schrieb it:

---

<div class="post-metadata">

**Author:** ![ititit](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/i/a5b964/32.png) [@ititit](https://www.ruby-forum.com/u/ititit)\
**Post date:** [September 23, 2008, 9:45pm UTC](https://www.ruby-forum.com/t/finding-distinct-months-using-find-by-sql/149340/3 "2008-09-23T21:45:54Z")

</div>

Thanks for the reply Johannes. I think you’re right about Postgresql  
not supporting monthname().

I managed to get round this using the following:  
@headlines\_month = Headline.find\_by\_sql(“SELECT DISTINCT  
DATE\_PART(‘year’, date) as year, DATE\_PART(‘month’, date) as month  
FROM headlines ORDER BY year DESC”)

I appreciate your taking time to reply.  
Best regards  
IRT

---

<div class="post-metadata">

**Author:** ![ititit](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/i/a5b964/32.png) [@ititit](https://www.ruby-forum.com/u/ititit)\
**Post date:** [September 24, 2008, 7:08pm UTC](https://www.ruby-forum.com/t/finding-distinct-months-using-find-by-sql/149340/4 "2008-09-24T19:08:07Z")

</div>

To display the month name you could either:

1. Create a function that would return the month name based on the  
month numeric value and call it from your SQL statement. This would  
allow you to use the same functionality in more than 1 statement,  
or…
2. Use a case clause. Not sure how the syntax would be in Postgresql.  
Something like this:  
case when DATE\_PART(‘month’, date) = 1 then ‘January’  
when DATE\_PART(‘month’, date) = 2 then ‘February’  
…  
end

Pepe