tags:

views:

174

answers:

5

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

+1  A: 

This should be what you're after:

SELECT FROM_UNIXTIME(my_unix_timestamp_column, '%Y') AS year FROM table_name WHERE year = 2004;
BipedalShark
I never realized FROM_UNIXTIME accepted format arguments, nice.
joebert
You can't reference year in the WHERE clause... It might work if you used `HAVING` instead, but then MySQL would have to work-out the YEAR for every row.
searlea
A: 

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.

jW
He specifically said he didn't want a PHP solution.
BipedalShark
but also said "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"and this is a solid solution as well that may not have been previously known.
jW
A: 

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'
pixeline
+1  A: 

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.

searlea
Note: the quotes aren't necessary; MySQL will happily handle `UNIX_TIMESTAMP(20080101)` etc. - I've no idea if this has any benefit for performance.
searlea
This is definitely the best answer. Ignore my own. ;)
BipedalShark
good stuff, thanks!
If you accept sealea's answer, it's proper to select it as the correct one (click on the checkmark).
BipedalShark