Page 1 of 1

php/mysql date question

Posted: Tue Jul 25, 2006 6:08 pm
by paultfh
How do I take a datetime type mysql and calculate how many days old it is?

Example:

$res = mysql_query("SELECT date FROM groups WHERE ID = {$arr['groupID']}");
$date = mysql_result($res2,0,0);

//here I want to calculate how many days old $date is

Thanks

Posted: Tue Jul 25, 2006 6:14 pm
by feyd
MySQL has an internal function called TO_DAYS()

Posted: Tue Jul 25, 2006 6:16 pm
by paultfh
How do I call it?

Posted: Tue Jul 25, 2006 6:16 pm
by RobertGonzalez
To answer your question, convert the returned date value to a timestamp using strtotime() then subtract that timestamp from time() then do some math on the result to find out how old it is.

I would say that a better way would be to calculate date_diff in the query using an alias to set up your difference.

Some useful recent threads on calculating date differences:
viewtopic.php?t=50540&highlight=datediff
viewtopic.php?t=50322&highlight=datediff
viewtopic.php?t=48468&highlight=datediff
viewtopic.php?t=46925&highlight=datediff

Posted: Tue Jul 25, 2006 6:19 pm
by Luke
I think this may be the most commonly asked question at this forum... you should be able to find the answer with the search function.

Posted: Tue Jul 25, 2006 6:42 pm
by paultfh
I did a search and tried this from what I read:

Code: Select all

$date1 = strtotime($date);
		$datenow = time();
		$diff = $datenow - $date1;
		$age = $diff/86400;
Result just needs formatting. Thanks guys

Posted: Tue Jul 25, 2006 6:47 pm
by John Cartwright
paultfh wrote:I did a search and tried this from what I read:

Code: Select all

$date1 = strtotime($date);
		$datenow = time();
		$diff = $datenow - $date1;
		$age = $diff/86400;
Result just needs formatting. Thanks guys
If your dates are already in a database, I find it much easier/better to let the database do the work for you.

Posted: Tue Jul 25, 2006 6:54 pm
by paultfh
Jcart wrote:
paultfh wrote:I did a search and tried this from what I read:

Code: Select all

$date1 = strtotime($date);
		$datenow = time();
		$diff = $datenow - $date1;
		$age = $diff/86400;
Result just needs formatting. Thanks guys
If your dates are already in a database, I find it much easier/better to let the database do the work for you.
Well, the date is extracted from the db in one page and sent to a form where I need the age calculation. Then the form posts the orginal date and inputs it into a seperate db. So I can't think of a way of doing it that way without another sql query.

Posted: Tue Jul 25, 2006 6:56 pm
by paultfh
For searchers this is the final code I used:

Code: Select all

//age calculation
		$date1 = strtotime($date);
		$datenow = time();
		$diff = $datenow - $date1;
		$diff /= 86400;
		
		//if age is less than 1 day output in hours else output in days
		if($diff < 1)
		{
			$diff *= 24;
			$age = round($diff,1);
			echo "<td width=\"85%\">".$age." hour(s)";
		}else{
			$age = round($diff,1);
			echo "<td width=\"85%\">".$age." day(s)";
		
      	        }