Time elapsed format in PHP or SQL

Question

What would be a good way to get the time elapsed for a tuple from a database like Stackoverflow or Facebook does? When < 1 minute elapsed display seconds elapsed, less than 1 hour display minutes etc. Can this be in MySQL with better performance than formatting in PHP?

May be duplicate of -> http://stackoverflow.com/questions/2915864/php-how-to-find-the-time-elapsed-since-a-date-time — Rikesh, Nov 09 '11 at 06:32

Tadeck · Accepted Answer · 2011-11-09T07:00:03.887

No. This PHP really best suited - you can get timestamp value from the database and (eventually) convert it into Unix epoch format like that:

SELECT UNIX_TIMESTAMP(`when`) FROM `posts` ORDER BY `id` DESC LIMIT 1;

and then calculate the difference. Calculating the difference, especially when dividing it into ranges, you would require MySQL to do something that is not really best suited for that.

Eventually you can also think about passing processing effort to the user - just try using JavaScript to determine the difference based on the timestamp. That way you can just use PHP to display value from database, without further processing on PHP's side.

Solution using JavaScript (jQuery)

This solution shifts processing effort into user and allows for constant updating of the timestamp at the same time. JavaScript could look like this:

jQuery('span[data-timestamp]').each(function(){
    var el = jQuery(this);
    var then = Math.floor(el.attr('data-timestamp'));
    var updateTime = function(){
        var now = Math.floor((new Date()) / 1000);
        el.html((now - then) + ' seconds ago');
    };
    setInterval(updateTime, 1000);
    updateTime();
});

and then every snippet looking like that:

<span data-timestamp="1320821800"></span>

will be converted into relative timestamp and updated every second (see this jsfiddle). If you want to use it, just make sure updateTime properly changes relative timestamp in seconds into one employing different units (minutes, hours, days, weeks, months, years etc. ...).

Yes - information formatting should happen as close to the user as possible (not in the database). — TehShrike, Nov 09 '11 at 09:16

score 0 · Answer 2 · answered Nov 09 '11 at 06:32

Ensure you also have a field that stores the time when the tuple is created in the database table, then using simple arithmetic (or date/time functions of MySQL, read your docs), select the difference (convert to seconds). The PHP side would then encodes the string to display based on that seconds value (it's lighter for PHP to do it than database server, I guess).

Time elapsed format in PHP or SQL

2 Answers2

Solution using JavaScript (jQuery)