can mysql find the year value of a unix timestamp?

Question

im storing the dates that entries were posted on in the db using a standard unix timestamp.

is it possible, using only a mysql query (no php logic), to select entries that were posted in a certain year?

id like to avoid retrieving ALL entries and then using php to filter on year value. i could store the year in a separate field of course, just curious about this

Answer 1

maybe use the FROM_UNIXTIME() function

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_from-unixtime

Answer 2

This should be what you're after:

SELECT FROM_UNIXTIME(my_unix_timestamp_column, '%Y') AS year FROM table_name WHERE year = 2004;

Answer 3

A better way to do that is to use a little bit of PHP to create the unix timestamp you want. If it is this year try

$timestamp = strtotime("January, 1, 2009");
$db->query("Select * from table where time < $timestamp");

This is just an example, but you will be able to quickly narrow your results and only do very minimal php logic.

Answer 4

Depends of your mysql version. 5.1 has this neat function FROM_UNIXTIME(unix_timestamp,format)

mysql> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),
    ->                      '%Y %D %M %h:%i:%s %x');


    -> '2007 30th November 10:30:59 2007'

Answer 5

You only want MySQL to do the hard work of extracting the year once:

SELECT your, columns
FROM posts
WHERE postdate BETWEEN UNIX_TIMESTAMP('20080101') AND (UNIX_TIMESTAMP('20090101')-1)

Obviously, this adapts easily to extract posts within a certain month or day or decade etc.