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
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
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:> 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 > > > >--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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
On 23 Sep, 19:46, "Johannes J. Schmidt"
<d...-xIc/GT63x2dWk0Htik3J/w@public.gmane.org>
wrote:> 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:
>
> > 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
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
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
On Sep 23, 3:45 pm, it <envnew...-gM/Ye1E23mwN+BqQ9rBEUg@public.gmane.org>
wrote:> 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
>
> On 23 Sep, 19:46, "Johannes J. Schmidt"
<d...-xIc/GT63x2dWk0Htik3J/w@public.gmane.org> wrote:
>
> > 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:
>
> > > 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
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---