[SOLVED] Sort for last 7 days

[Vivid Lust]

Could anyone tell me how I could select records from the last 7 days which are stored in a MySQL database? I have a column named "time" using the php time() fuction. Could someone tell me the sql needed to do this?

 

thanks,

 

Jake.

[Vivid Lust]

any ideas? thanks.

[Daniel0]

Get the timestamp from seven days ago and select all rows with a timestamp greater than or equal to what you just computed.

[Baronen]

SELECT * FROM tbl WHERE DATE_FORMAT(datefield, '%Y-%m-%d') > CURDATE() - INTERVAL 7 DAY

 

This is one way, i think it should work, not tested though

[Vivid Lust]

Hi, the query is returning 0 rows

 

select DISTINCT `URL` from hits WHERE `cat`="language" AND DATE_FORMAT(`Time`, '%Y-%m-%d') > CURDATE() - INTERVAL 7 DAY

 

here is an example value of Time:

 

1256130486 

 

 

Hope someone can help Thanks again!

[Daniel0]

Get the timestamp from seven days ago

 

$sevenDaysAgo = strtotime('-7 days');

 

and select all rows with a timestamp greater than or equal to what you just computed.

 

$query = 'SELECT DISTINCT `URL` FROM hits WHERE `cat`="language" AND `Time` >= ' . $sevenDaysAgo;
$result = someSortOfFunctionThatExecutesQueries($query);