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

Question

Here is my code so far:

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

I want make pages based on the year, like, 2010 , 2009, 2008 and so on. The database saves the date as UNIX_Timestamp. Not sure how to query a recordset with a Year parameter?

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

Thanks in advance. I'm baffled.

Answer 1

You'll want something like WHERE YEAR(FROM_UNIXTIME(date_field)) = 2010.

Answer 2

Try:

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

Answer 3

You can use the FROM_UNIXTIME() function, but be aware that this will not use an index on the date column, if one exists:

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

For your query to be sargable, you could use the the UNIX_TIMESTAMP() function instead:

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