How to group by year when I have a columns named date in the format dd/mm/yyyy

Question

I have a table in which a date column is there in the format dd/mm/yyyy. I have an accordion in which I want to show years like 2014 then 2013 and so on. When I click on 2014(my accordion) it will expand. If I had a column containing only year then it would have been easier. Now I am confused whether to simply create a columns named year. or do something in php to get this think working.

Basically I have to display a UI which customers purchase history will be shown. Year wise. When I click on 2014 the pane would expand and inside, all his invoices would be visible.

Any help is appreciated Thanks in advance

Answer 1

You can manipulate the results with php to get your desired results.

$result = some_query_result(); //however you're retreiving the data
foreach($result as $row)
{
    $dateArray = explode('/', $row['date']); //whatever the date field is.
    $year = end($dateArray);
    //now you have the year - do with it as you please.
    //maybe create an array containing the data or something
    $theData[$year][] = $row; //now you'll have data keyed by year in the array
}

Don't know exactly what you're doing with it after or how you're creating the html, but this should get you on the right track.

Answer 2

Try below code:

SELECT DATE_FORMAT( `date` , '%Y' ) AS d_yr
FROM tablename
GROUP BY `d_yr` ASC 

OR

SELECT *
FROM tablename
GROUP BY DATE_FORMAT( `date` , '%Y' ) ASC 

Answer 3

You can GROUP BY the result of a function. I would prefer YEAR() since AFAIK it's the fastest way possible. Year will only extract the YEAR portion of the date. Here is an example on how to do that:

SELECT date_column FROM ... GROUP BY YEAR(date_column)

Note that conversion is slow and will result in very slow queries. Instead you might want to have a date column which will force the use of date indexing instead of string indexing and will result in a much better performance.