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;
精彩评论