SQL - Check which years contain data

Question

How to select only years from mysql database (but only those years which contain data)?

The PHP time() function is used to populate the date in the database. There are more then 10,000 records stored since 2006...

I would like to categorize data by date (firstly, user selects year, then month, than day...). I'd also like to be able to use a similar approach for selection by months and/or days.

Answer 1

Check out the MySQL Extract Function.

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function%5Fextract

Answer 2

There are MySQL functions that make it possible to extract year/month/date information from a date, but they work on the MySQL's date type. So first you need to convert your UNIX timestamp to date, using FROM_UNIXTIME(date) and then extract the part of the information you want. See the docs for a list of available date functions. So, for example to get all years you can use:

SELECT DISTINCT YEAR(FROM_UNIXTIME(date)) FORM table;

If you want to later filter the actual results, you can use this to get all rows from year 2009:

SELECT ... WHERE YEAR(FROM_UNIXTIME(date)) = 2009

Unless you have a good reason for storing the timestamp in the database, I'd suggest to convert the column to the MySQL's datetime type. That will allow you to use an index to speed up the query. MySQL can't create indexes using specific functions, so you can't use YEAR() in the query, but you can do for example:

SELECT ... WHERE date >= 2009 AND date <= 2009;

Answer 3

How to select only years from mysql database (but only those years which contain data)?

SELECT  DISTINCT(YEAR(datecolumn))
FROM    mytable

I would like to categorize data by date (firstly, user selects year, then month, than day...). I'd also like to be able to use a similar approach for selection by months and/or days.

SELECT  DISTINCT(MONTH(datecolumn))
FROM    mytable
WHERE   datecolumn >= CAST(CONCAT($year, '-01-01') AS DATETIME)
        AND datecolumn < CAST(CONCAT($year + 1, '-01-01') AS DATETIME)


SELECT  DISTINCT(DAY(datecolumn))
FROM    mytable
WHERE   datecolumn >= CAST(CONCAT_WS('-', $year, $month, '01') AS DATETIME)
        AND datecolumn < CAST(CONCAT_WS('-', $year, $month, '01') AS DATETIME) + INTERVAL 1 MONTH

If you store what PHP time() returns in your columns, use this:

SELECT  DISTINCT(YEAR(FROM_UNIXTIME(datecolumn)))
FROM    mytable

SELECT  DISTINCT(MONTH(FROM_UNIXTIME(datecolumn)))
FROM    mytable
WHERE   datecolumn >= CAST(CONCAT($year, '-01-01') AS DATETIME)
        AND datecolumn < CAST(CONCAT($year + 1, '-01-01') AS DATETIME)

SELECT  DISTINCT(DAY(FROM_UNIXTIME(datecolumn)))
FROM    mytable
WHERE   datecolumn >= UNIX_TIMESTAMP(CAST(CONCAT_WS('-', $year, $month, '01') AS DATETIME))
        AND datecolumn < UNIX_TIMESTAMP(CAST(CONCAT_WS('-', $year, $month, '01') AS DATETIME) + INTERVAL 1 MONTH)

Note that the conditions we use here are sargable: datecolumn is the only thing on one side of the expression, and an index can be used to search for it.

Answer 4

I think the sql you want is below. I tested these statements on my data which is stores as a mysql date, but the from_unixtime should do the conversion so that the rest works as you want.

-- Get year and counts
SELECT DATE_FORMAT(FROM_UNIXTIME(the_date), '%Y') AS raw_date,
       DATE_FORMAT(FROM_UNIXTIME(the_date), '%Y') AS format_date,
       COUNT(FROM_UNIXTIME(the_date)) AS num_activities
  FROM date_table
 GROUP BY DATE_FORMAT(FROM_UNIXTIME(the_date), '%Y'),
          DATE_FORMAT(FROM_UNIXTIME(the_date), '%Y');

-- Get month/year and counts     
SELECT DATE_FORMAT(FROM_UNIXTIME(the_date), '%Y%m') AS raw_date,
       DATE_FORMAT(FROM_UNIXTIME(the_date), '%M %Y') AS format_date,
       COUNT(FROM_UNIXTIME(the_date)) AS num_activities
  FROM date_table
 GROUP BY DATE_FORMAT(FROM_UNIXTIME(the_date), '%Y%m'),
          DATE_FORMAT(FROM_UNIXTIME(the_date), '%M %Y');

-- Get date and counts
SELECT DATE_FORMAT(FROM_UNIXTIME(the_date), '%Y%m%d') AS raw_date,
       DATE_FORMAT(FROM_UNIXTIME(the_date), '%M %d, %Y') AS format_date,
       COUNT(FROM_UNIXTIME(the_date)) AS num_activities
  FROM date_table
 GROUP BY DATE_FORMAT(FROM_UNIXTIME(the_date), '%Y%m%d'),
          DATE_FORMAT(FROM_UNIXTIME(the_date), '%M %d, %Y');