How to group by month+year?

Question

http://www.bioshapebenefits.nl/bioshapebenefits/en/news/donations.html

As you see, here I have a long list of entries, sorted by date, very simple. So in mysql there would only be the columns 'name' and 'date', for example. And if I'd want to make a list out of it with php that would also be very simple, just sorting by date.

But how would I put the months+years in between, as shown in the example? Like:

June 2010

• Trala
• Lala
• Wala

May 2010

• Trala
• Lala
• Wala

Answer 1

In SQL Server it would be as simple as the following, I'd imagine mysql has something similar:

GROUP BY Year(Date), Month(Date)

Answer 2

There's a dozen ways to skin this cat, but my approach would probably be like this (all rough code snippets, not a full implementation)

First, the query

select `name`
     , month(`date`) as date_month
     , year(`date`) as date_year
  from [Table]
 order by `date` desc

Then, organize the data into the desired logical groups

$templateData = array();
foreach ( $rows as $row )
{
  $templateData[$row->date_year][$row->date_month][] = $row->name;
}

Then, in a template

<?php foreach ( $templateData as $year => $months ) : ?>
  <?php foreach ( $months as $month => $names ) : ?>
    <h2><?php echo $month, ' ', $year; ?></h2>
    <ul>
      <?php foreach ( $names as $name ) : ?>
        <li><?php echo $name; ?></li>
      <?php endforeach; ?>
    </ul>
  <?php endforeach; ?>
<?php endforeach; ?>

Answer 3

We do tonnage of this month grouping. I use this expression to truncate arbitrary timestamps to timestamps that represent the first of the month.

   TIMESTAMP(DATE_FORMAT(action_time,'%Y-%m-01'))

This does the same thing as the Oracle-ism TRUNC(action_time,'MM')

For example you could do

SELECT COUNT(*) NUM, TIMESTAMP(DATE_FORMAT(t.action_time,'%Y-%m-01')) MONTH
FROM TABLE t
GROUP BY TIMESTAMP(DATE_FORMAT(t.action_time,'%Y-%m-01'))

This has the conceptual advantage that it maintains the timestampiness of the month-truncated data, so if you need to you can still compute time differences and do other timey and datey things if you need to.

As ravern pointed out, there's a small performance penalty (but it isn't bad!). Some of our table columns are loaded using this function so they're already month-truncated.