formating time passed

fr8

when i insert new date into mysql im adding a timestap as well.

im trying to format how old the row is when i output it.

convert this:
20050612231933

to this:
2 days, 19 hours ago

should timestamp be used for this or should i use datetime? any ideas?

wilku

Here's some example:

<?php
$str = '20050616110000';

$now = time();
$year = substr($str,0,4);
$month = substr($str,4,2);
$day = substr($str,6,2);
$hour = substr($str,8,2);
$min = substr($str,10,2);
$sec = substr($str,12,2);
$when = mktime($hour,$min,$sec,$month,$day,$year);
$diff = $now - $when;
$d = floor($diff/(24*60*60));
$remain = $diff%(24*60*60);
$h = floor($remain/(60*60));
$remain = $remain%(60*60);
$m = floor($remain/60);
$s= $remain%60;
echo date("Y/m/d H:i:s",$now)." - $d days, $h hours, $m minutes, $s seconds have passed
since ".date("Y/m/d H:i:s",$when);
?>

The problem would begin when you want to add months and years (as they don't have fixed length.

leatherback

Hi,

you need to use the date_format function to do this (Scroll down on that page)

It will do exactly what you want.:

mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y');
-> 'Saturday October 1997'DATE_FORMAT('1997-10-04 22:23:00',
'%D %y %a %d %m %b %j');
-> '4th 97 Sat 04 10 Oct 277'

Instead of the actual date, you are passing a reference to yur date colum:

SELECT DATE_FORMAT('now()-TIME_STAMP_COLUM', '%W %M %Y') as date from...