Help wanted with Date Calculations

[paragtim]

Hi,

I'm trying to do a table that returns the number of pages created over a period of 7 days. The table stores the date the field was created in Unix Format (10digits), the page name.

How do I write the query that will return the data and display it in a table. I can get the all the pages and limit the display to x records using the following code:

$result = Mysql_query( "Select user, hits,(FROM_UNIXTIME(created)) From tiki_pages ORDER BY created desc Limit 0, 20" );

But I cant work out how to do the date calculations.

Any help would be appreciated

Tim

[ixalmida]

I don't know if this is the best way to do it, but this usually works for me:

// Get today's date in UNIX time...
$unixtoday = mktime(0, 0, 0, date("m"), date("d"), date("Y"));

// Subtract a week's worth of seconds...
$unixlastweek = $unixtoday - (7 * 24 * 60 * 60);  // days * hours * minutes * seconds

$query = "SELECT user, hits, created FROM tiki_pages WHERE created > $unixlastweek ORDER BY created DESC LIMIT 0, 20";

[Weedpacket]

MySQL has some date and time functions you could look up: you'd need to convert from Unix epoch format as you already are, and then you can use those functions in the query.