开发者

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

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.


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


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')


Try:

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

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜