Selecting distinct dates for a blog archive

I'm using Rails for a blog engine. I'm implementing an archive feature that archives based on unique month and years where a post has been published.开发者_如何学编程

Here's the cucumber feature:

  Scenario: Displaying an archive menu from distinct posts months and years 
   Given the following posts exists for the blog "Blondinbella":
         | Title           | Published at          |
         | Redbull         | 1 March 2010 11:00    |
         | Tuesday outfit  | 2 January 2010 11:00  |
         | Monday outfit   | 1 January 2010 11:00  |
         | Another outfit  | 1 December 2009 11:00 |
    When I visit the blog "Blondinbella"
    Then I should see "March 2010" in the archive menu
     And I should see "January 2010" in the archive menu
     And I should see "December 2009" in the archive menu
     But I should not see "February 2010" in the archive menu

I am having a hard time figuring out the best approach for this. Should I go hardcore with a SQL-query and if so how would that look? (Using PostgreSQL)

Or is there a way of doing this smoothly just using pure Rails?

---

The pure Ruby way:

Post.all.group_by { |post| post.published_at.strftime("%B %Y") }.map { |group| group.first.strftime("%B %Y") }

Which will be a heavy load if you have a lot of posts. So you could do it in SQL:

Post.select("DISTINCT foo AS month").map(&:month)

Replace the foo with something to format the date (I don't know how to do this by heart, so you'll have to look that up yourself)

---

With postgresql, I found using the date_trunc method the most useful.

eg:

Post.select("DISTINCT date_trunc('month', published_at) as month").collect(&:month)

I found it also makes sense to use the AT TIME ZONE construct to convert the date to your local timezone before doing the truncation:

Post.select("DISTINCT date_trunc('month', published_at AT TIME ZONE 'NZST') as month").collect(&:month)