How to select UNIX date = "2010" in mysql query?

Question

Here is my code:

SELECT `date`, title, category, url
FROM cute_news
WHERE category = '4'
ORDER BY `date` DESC

I want to create pages based on the year, e.g. 2010, 2009, 2008, etc. The database saves the date as UNIX_Timestamp. Not sure how to query a recordset with the Year parameter?

WHERE unix_timestamp(YEAR) = '2010' or something???

Thanks in advance. I am puzzled.

Answer 1

You need something like WHERE YEAR(FROM_UNIXTIME(date_field)) = 2010.

Answer 2

You can use the function FROM_UNIXTIME(), but keep in mind that this will not use the index in the column dateif one exists:

... WHERE YEAR(FROM_UNIXTIME(`date`)) = 2010

For your sargable request you can use UNIX_TIMESTAMP():

... WHERE `date` >= UNIX_TIMESTAMP('2010-01-01 00:00:00') AND 
          `date` < UNIX_TIMESTAMP('2011-01-01 00:00:00')

Answer 3

Try:

WHERE YEAR(FROM_UNIXTIME(`date`)) = 2010;