Create timestamps similar to Facebook?

Question

Quite simply, the 2 seconds ago, 1 day ago, 2 weeks ago, etc. What is the most optimal way to accomplish this?

Using SQL Server for DB. Is this done at a query level? Or javascript on page load?

Also, how it updates every so often by itself... Do you think it's all javascript on client-side for the user experience, or a lot of ajax requests recalculating?

Just looking for advice on the best way to reproduce this type of timestamp.

I have a commenting system on my site and I'm simply using the values from the SQL Date_Created field that I have in the table.

Thank you all so much.

Well I have a code snippet for the "ago" type time stamp for PHP, and making something similar in JS wouldn't be hard. In fact, it'd be easier than PHP. JS' Date class is much more versatile. However, the big question is, would making this a JS feature bring the speed of your page down more than the expense of having the server process it? Either way you go would seem fine, as long as you account for the "type" of User Browsers/systems coming to your site and the population of users at any one time. I would recommend letting the server do it myself. — SpYk3HH, Aug 12 '13 at 18:59
You could look [*here*](http://stackoverflow.com/questions/1416697/converting-timestamp-to-time-ago-in-php-e-g-1-day-ago-2-days-ago). — SpYk3HH, Aug 12 '13 at 19:00
look here for solution in SQL: [link](http://stackoverflow.com/questions/7323883/sql-query-that-displays-time-ago-dates-like-one-week-ago-two-weeks-ago) — BWS, Aug 12 '13 at 19:03

score 1 · Accepted Answer · answered Aug 12 '13 at 19:21

You could use this SQL only solution (sqlfiddle demo):

SELECT  ev.*,
        CASE 
            WHEN DATEDIFF(SECOND,ev.EventDate,GETDATE()) BETWEEN 0 AND 59
                THEN CONVERT(VARCHAR(25),DATEDIFF(SECOND,ev.EventDate,GETDATE())) + ' second' + CASE WHEN DATEDIFF(SECOND,ev.EventDate,GETDATE())>1 THEN 's' ELSE '' END
            WHEN DATEDIFF(MINUTE,ev.EventDate,GETDATE()) BETWEEN 0 AND 59
                THEN CONVERT(VARCHAR(25),DATEDIFF(MINUTE,ev.EventDate,GETDATE())) + ' minute' + CASE WHEN DATEDIFF(MINUTE,ev.EventDate,GETDATE())>1 THEN 's' ELSE '' END
            WHEN DATEDIFF(HOUR,ev.EventDate,GETDATE()) BETWEEN 0 AND 24
                THEN CONVERT(VARCHAR(25),DATEDIFF(HOUR,ev.EventDate,GETDATE())) + ' hour' + CASE WHEN DATEDIFF(HOUR,ev.EventDate,GETDATE())>1 THEN 's' ELSE '' END
            ELSE 
                CONVERT(VARCHAR(25),DATEDIFF(DAY,ev.EventDate,GETDATE())) + ' day' + CASE WHEN DATEDIFF(DAY,ev.EventDate,GETDATE())>1 THEN 's' ELSE '' END
        END AS Result
FROM    dbo.MyEvent ev;
GO
/*
EventID EventDate               Result
------- ----------------------- ---------
1       2013-08-12 22:20:03.323 5 seconds
2       2013-08-12 22:16:08.327 4 minutes
3       2013-08-12 19:20:08.327 3 hours
4       2013-08-09 22:20:08.330 3 days
5       2013-08-10 22:20:08.337 2 days
*/

Thank you. I was able to use this as an immediate solution. – user1447679 Aug 12 '13 at 21:33 — user1447679, Aug 12 '13 at 21:33

score 0 · Answer 2 · answered Aug 12 '13 at 19:02

Here is a link to EXACTLY what I think you might be looking for!

http://snipplr.com/view/37578/

<?php
    function nicetime($date) {
        if(empty($date)) {
            return "No date provided";
        }

        $periods = array("second", "minute", "hour", "day", "week", "month", "year", "decade");
        $lengths = array("60","60","24","7","4.35","12","10");

        $now = time();
        $unix_date = strtotime($date);

        // check validity of date
        if(empty($unix_date)) return "Bad date";

        // is it future date or past date
        if($now > $unix_date) {    
            $difference = $now - $unix_date;
            $tense = "ago";
        }
        else {
            $difference = $unix_date - $now;
            $tense = "from now";
        }

        for($j = 0; $difference >= $lengths[$j] && $j < count($lengths)-1; $j++) $difference /= $lengths[$j];

        $difference = round($difference);

        if($difference != 1) $periods[$j].= "s";

        return "$difference $periods[$j] {$tense}";
    }

    /*
    $date = "2009-03-04 17:45";
    $result = nicetime($date); // 2 days ago
    */

?>

score 0 · Answer 3 · answered Aug 12 '13 at 19:11

What I'd do is, in JavaScript, look for all timestamps on the page and compare them with the current time and process that difference to output what you want. You say you're using jQuery, so you could do something like this. Say you display timestamps in time elements (which you probably should by the way):

$('time').each(function () {
    // Gets timestamp time and current time in milliseconds
    var time = Date.parse($(this).attr('datatime')),
        currentTime = new Date().getTime(),
        difference = (currentTime - time) / 1000;

    // Use the difference to update the timestamp value accordingly
    if (difference < 60) {
        $(this).val(difference + ' seconds');
    } else if (difference > 60 && difference < 3600) {
        $(this).val(difference / 60 + ' minutes');
    } else if (difference > 3600 && difference < 86400) {
        // You get the idea
        // ...
    }
});

This way you avoid making unnecessary and quite expensive calls to the server to update them each time. You just retrieve them once at first and then let the users' browser update them on it's own.

score 0 · Answer 4 · answered Aug 12 '13 at 20:00

--Another SQL approach

declare @datediffseconds int declare @datetime datetime

SELECT @datetime = '2013-06-11 14:39:58.947' SELECT @datediffseconds = DATEDIFF(SECOND,@datetime, GETDATE())

SELECT CASE WHEN @datediffseconds < 60 THEN CONVERT(varchar(20),(@datediffseconds)) + ' seconds ago' WHEN @datediffseconds < 3600 THEN CONVERT(varchar(20),(@datediffseconds)/60) + ' minutes ago' WHEN @datediffseconds < 86400 THEN CONVERT(varchar(20),(@datediffseconds)/3600) + ' hours ago' WHEN @datediffseconds < 604800 THEN CONVERT(varchar(20),(@datediffseconds)/86400) + ' days ago' WHEN @datediffseconds < 31449600 THEN CONVERT(varchar(20),(@datediffseconds)/604800) + ' weeks ago' END

Create timestamps similar to Facebook?

4 Answers4