Difference of date in timestamp and normal dates in MYSQL
I have a table with two date columns, one is a timestamp (for example 1359380165) and the other is in a normal date and time value (for example, 2013-01-28 08:32:53).
I want to find the time span between now (current) and these dates given above. So the results will be e.g.
- Daniel changed password 5 minutes ago
- Jeniffer deleted her phone number 3 days ago.
Any ideas?
+3
source to share
2 answers
This function should do something similar to what you need, try it, just need a unix timestamp passed to it like your "1359380165".
function getRelativeTime($timestamp)
{
$timeDifference = time() - $timestamp;
$timePeriods = array('second', 'minute', 'hour', 'day', 'week','month', 'years', 'decade');
$timeLengths = array('60', '60', '24', '7', '4.35', '12', '10');
if ($timeDifference > 0)
{
$timeSuffix = 'ago';
}
else
{
$timeDifference = -$timeDifference;
$timeSuffix = 'to go';
}
for($i = 0; $timeDifference >= $timeLengths[$i]; $i++)
{
$timeDifference/= $timeLengths[$i];
$timeDifference = round($timeDifference);
}
if($timeDifference != 1) $timePeriods[$i].= 's';
return $timeDifference . ' ' . $timePeriods[$i] . ' ' . $timeSuffix;
}
+2
source to share
Assuming a table like this:
tybae
id int unsigned not null auto_increment primary key
username varchar(45) not null
utime int
dtime timestamp
Why not just structure the request so that PHP gets both timestamps in the same format? Something like that:
SELECT id, username, FROM_UNIXTIME(utime, '%Y-%m-%d %H.%i.%s') AS ut, dtime AS dt FROM mytable;
or
SELECT id, username, utime AS ut, UNIX_TIMESTAMP(dtime) as dt FROM mytable;
Then you can handle both ut and dt in the same way.
There is a nice relative function of time here . Unix timestamp required.
0
source to share