php/mysql date question

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
paultfh
Forum Commoner
Posts: 31
Joined: Thu Jul 20, 2006 6:24 pm

php/mysql date question

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

MySQL has an internal function called TO_DAYS()
paultfh
Forum Commoner
Posts: 31
Joined: Thu Jul 20, 2006 6:24 pm

Post by paultfh »

How do I call it?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post 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.
paultfh
Forum Commoner
Posts: 31
Joined: Thu Jul 20, 2006 6:24 pm

Post 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
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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.
paultfh
Forum Commoner
Posts: 31
Joined: Thu Jul 20, 2006 6:24 pm

Post 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.
paultfh
Forum Commoner
Posts: 31
Joined: Thu Jul 20, 2006 6:24 pm

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