Get pass date && time from TIMESTAMP field using PHP

Question

I have a field in a DB called created_at and store data in this format:

1980-11-28 04:05:25

So I want to get how many time has been passed since that date as for example: 3 seconds || 5 minutes || 22 hours || 1 days || 6 weeks || 1 month || 3 years (notice the || so can be any of this options not all of them). I don't know if I can get this directly from a query meaning using SQL language and I check this [1] but can't find the right function to do this. So I think to get it using PHP but either don't know how to. Can any help me?

[1] http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

possible duplicate of [How to calculate the difference between two dates using PHP?](http://stackoverflow.com/questions/676824/how-to-calculate-the-difference-between-two-dates-using-php) — John Conde, Aug 15 '12 at 13:33
When you say 'store data in this format...' do you mean it is a string or it is a datetime in the mysql column? if it is a datetime field you can use [unix_timestamp](http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_unix-timestamp) to convert it to a unix timestamp (which is stored as seconds) and the rest is trivial. If you are storing this as a string with the data you posted in your question - then for goodness sake, switch to a proper column data type :) You can also do it directly in mysql if it is a datetime field with the [datediff](http://dev.mysql.com/doc/r — Fluffeh, Aug 15 '12 at 13:35
Yes it's stored as datetime because I use NOW() so I think isn't a string and by use UNIX_TIMESTAMP you mean somethink like SELECT UNIX_TIMESTAMP(created_at) AS date .... ? Trivial? Could you post an example? Thanks — ReynierPM, Aug 15 '12 at 13:38
@JohnConde thanks the post you leave me is what I was looking for — ReynierPM, Aug 15 '12 at 14:46

score 1 · Accepted Answer · answered Aug 15 '12 at 15:13

I'm unsure if there is a native PHP function that allows you to do this, however you can make one. I found this one at http://css-tricks.com/snippets/php/time-ago-function/ and edited it to make it a bit more functional. Using this we can just simply put your created_at string through it and get the result.

// The function to get the final string
function timeago($tm,$lim=0) {
   $cur_tm = time(); $dif = $cur_tm-$tm;
   $pds = array('second','minute','hour','day','week','month','year','decade');
   $lngh = array(1,60,3600,86400,604800,2630880,31570560,315705600);
   for($v = sizeof($lngh)-1; ($v >= 0)&&(($no = $dif/$lngh[$v])<=1); $v--); if($v < 0) $v = 0; $_tm = $cur_tm-($dif%$lngh[$v]);
   $no = floor($no); if($no <> 1) $pds[$v] .='s'; $x=sprintf("%d %s",$no,$pds[$v]);
   if($lim>1 && ($v >= 1)&&(($cur_tm-$_tm) > 0)) $x .= ', ' . timeago($_tm,$lim-1); else $x .= ' ago';
   return $x;
}

// Run the MySQL query to get the string
$query = mysql_query("SELECT `created_at` FROM `table` LIMIT 1");
// Put the string into a PHP variable
$created_at = mysql_result($query,0);

// Show the results
echo timeago($created_at);
// This will output something like  '4 years ago' or '12 seconds ago'

// You can fine tune how accurate you want the function to make your 'time ago' string to be by adding a number as the second variable for the function
// For example:
echo timeago($created_at, 6);
// This will output something like '4 decades, 2 years, 7 months, 1 week, 15 hours, 49 minutes, 12 seconds ago'

Nice I'll give a try but I found that CodeIgniter framework has his own function to deal with this in Date helper so I'm using this one by now, anyway thanks — ReynierPM, Aug 15 '12 at 15:33

Get pass date && time from TIMESTAMP field using PHP

1 Answers1