I am stuck with the problem of getting the difference of two datetimes (or they may be timestamps) to show how long ago the record was updated. Let's say my table has field `modified` which is datetime and means the date and time the record was last modified. I try to do it in a way like this:

select *, date_format(now() - modified, "%H:%M:%S") as mod from table

But this returns records with empty `mod` field. I feel taht there is a way to do it in a single query, without post-processing dates in php, but I cannot understand what am I doing wrong.

OK, seems like I found some solution, but it seems to work wrong

select *, now() as now, from_unixtime(unix_timestamp(now()) - unix_timestamp(accessed), "%H:%i:%S") as ftd from search

returns

[16] => stdClass Object
        (
            [id] => 52359
            [query] => AWInstall
            [counter] => 0
            [cached] => 2006-08-17 18:09:35
            [accessed] => 2006-08-21 14:20:37
            [now] => 2006-08-21 14:45:47
            [ftd] => 06:25:10
        )

As we can see, the difference between now() and accessed is 25 minutes and 10 seconds, where does it get those 6 extra hours?

Yep. Found the bug. The UNIX epoch begins at 6:00am on 1st January 1970. That 6 hours are added to my `ftd`, so I just add 64800 seconds and get the right formatted date.