[Resolved] Comparing timestamps (to return elapsed time)

vaaaska

Technically, this is easy, but I'm not sure why I can't make it work. I'm trying to compare a timestamp value in the db to the current time and then return the elapsed time as either minutes, hours, days or years ago...

Anyways, this is tossing things around on me...example:

$now = 20040528120000 // 05/28/2004 Noon
$dbtime = 30040528110100 // 05/28/2004 11:01:00am

Now, if I find the difference between ($now - $dbtime) I get 9900. This should be 59 minutes, but when I divide (9900 / 60) I get 165 minutes. Not the result I would expect.

So, I'm missing something here I guess, but I'm not sure where my logic went off course.

???

ps: my timestamps, if it isn't obvious, take the form YmdHis...isn't that right?

Weedpacket

Well off course; subtracting a MySQL timestamp from another will just let you know which one was earlier or later than the other; the number itself doesn't mean anything (MySQL timestamps don't represent elapsed seconds the way Unix timestamps do; the first four digits represent the year, the next two represent the month, the next two represent the day, the next two represent the hour, the next two represent the minute, and the last two represent the second - and how are you supposed to carry from one to the next?)

Try looking through MySQL's time and date handling functions and see if something's there. If not, try converting to Unix timestamps and subtracting those.

vaaaska

Noooooooooooooooooooo...

Why did I think that these were the same as Unix timestamps I ask...I have no answer for that shrugs...

Will do, merci beacoup...v

vaaaska

It's not rocket science, but I'm posting the fast function for converting to a timestamp from YmdHis format...

function makeTimestamp($timestamp) {
$hr = substr($timestamp,8,2);
$min = substr($timestamp,10,2);
$sec = substr($timestamp,12,2);
$day = substr($timestamp,6,2);
$mn = substr($timestamp,4,2);
$yr = substr($timestamp,0,4);
$newtime = mktime($hr, $min, $sec, $day, $mn, $yr);
return $newtime;
}