从数据库时间戳中提取详细信息

Question

I'm learning about timestamps. I can find lots of infomation about extracting data from current timestamps but little about querying a database timestamp. For example, I'd like to perform the following (the syntax is not correct, but should hopefully illustrate my question)

SELECT * FROM database where timestamp DAY('12') AND MONTH('01')

or

SELECT * FROM database where timestamp MONTH('01') AND YEAR('2009')

can you point me in the right direction?

Answer 1

You can use the MySQL function FROM_UNIXTIME().

SELECT * FROM database WHERE DAY(FROM_UNIXTIME(timestamp)) = 12 
    AND MONTH(FROM_UNIXTIME(timestamp)) = 1

SELECT * FROM database WHERE MONTH(FROM_UNIXTIME(timestamp)) = 1
    AND YEAR(FROM_UNIXTIME(timestamp)) = 2009