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)";
}