Q:

MYSQL query which years have records

I have a table with a date field, I need to find out which years have records and output only the list of the years e.g.( [2011,2012,2013] ), the only way I have imagined is to get all rows and then analyze in a php loop.
Does anybody knows a way to achieve this with pure SQL, or will I have to analyze the data with a php function?

A:

Try doing:
SELECT YEAR(dateColumn)
FROM yourTable
GROUP BY YEAR

A:

SELECT DISTINCT YEAR(datecolumn) FROM yourtable

YEAR() is a MySQL function that will return the year corresponding to the current date stored in your column.
SELECT DISTINCT will return only unique values for the result of YEAR(datecolumn). More information can be found in this tutorial

If you need to output the list itself in one SQL query Linger's answer is clearly the way to go.

A:

You state that you want a list like [2011,2012,2013].  The following SQL Fiddle demonstrates how you can achieve such a thing in one field:
SELECT CONCAT('[', GROUP_CONCAT(MQ.MyYear), ']') AS YearsList
FROM 
(
  SELECT DISTINCT YEAR(MyDateField) AS MyYear 
  FROM MyTable
) AS MQ

In the above example I use a sub query to get all of the unique years found in the table.  Then I use GROUP_CONCAT to combine them all together separating each year with a ,.  Then I use CONCAT to add the brackets at the front and end of the string.

