Extracting details from a database timestamp

Question

Hi,

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

Answer 2

For the second one, a more efficient way would be like this:

SELECT *
FROM database
WHERE timestamp BETWEEN
    UNIX_TIMESTAMP('2009-01-01 00:00:00')
    AND
    UNIX_TIMESTAMP('2009-01-31 23:59:59')

Answer 3

Guys - big thanks works a treat ;-)