How to get timestamp of MySQL table in relative time on website

Question

I want to know, step by step, the way to transform a timestamp in my mysql table to a relative time (4 seconds ago, 5 months ago...) on my website.

For information, my timestamp column is named creation, and my table is named users.

The code I tried:

function relativedate($secs) {
    $second = 1;
    $minute = 60;
    $hour = 60*60;
    $day = 60*60*24;
    $week = 60*60*24*7;
    $month = 60*60*24*7*30;
    $year = 60*60*24*7*30*365;

    if ($secs <= 0) { $output = "now";
    }elseif ($secs > $second && $secs < $minute) { $output = round($secs/$second)." second";
    }elseif ($secs >= $minute && $secs < $hour) { $output = round($secs/$minute)." minute";
    }elseif ($secs >= $hour && $secs < $day) { $output = round($secs/$hour)." hour";
    }elseif ($secs >= $day && $secs < $week) { $output = round($secs/$day)." day";
    }elseif ($secs >= $week && $secs < $month) { $output = round($secs/$week)." week";
    }elseif ($secs >= $month && $secs < $year) { $output = round($secs/$month)." month";
    }elseif ($secs >= $year && $secs < $year*10) { $output = round($secs/$year)." year";
    }else{ $output = " more than a decade ago"; }

    if ($output <> "now"){
        $output = (substr($output,0,2)<>"1 ") ? $output."s" : $output;
    }
    return $output;
}



echo relativedate(60); // 1 minute

I found multiple code and it didn't work... I only copy/paste, I don't know how to integrate them... — , Jun 27 '13 at 05:09
And what is not working with this code ? In what format you getting time from mysql ? — Rikesh, Jun 27 '13 at 05:15
The «echo relativedate(60);» show only «1 minute»... it stuck there. — , Jun 27 '13 at 05:17
I want to take the timestamp in my database, and show it in a format such as «3 minutes ago...» — , Jun 27 '13 at 05:20

score 3 · Accepted Answer · answered Jun 27 '13 at 05:23

3

Use CASE to display the relative time/date. I created the table 'sample' in this code.

SELECT *, CASE
WHEN creation between date_sub(now(), INTERVAL 60 minute) and now() THEN concat(minute(TIMEDIFF(now(), creation)), ' minutes ago')
WHEN datediff(now(), creation) = 1 THEN 'Yesterday'
WHEN creation between date_sub(now(), INTERVAL 24 hour) and now() THEN concat(hour(TIMEDIFF(NOW(), creation)), ' hours ago')
ELSE date_format(creation, '%a, %m/%d/%y')
END as date FROM sample

This will make a new column 'date' that you can use to output the relative time.

answered Jun 27 '13 at 05:23

Jm Verastigue

408
4
9

You should include this your query. You may be using something like SELECT * FROM sample there, change it to include the CASE clause and output the converted row(date, in my example). – Jm Verastigue Jun 27 '13 at 05:29
In your mysql query. You just need to add the CASE clause instead of the 'creation' column. This will change the dates into relative time. – Jm Verastigue Jun 27 '13 at 05:43
To display data on your website, you create a query (mysqli_query) then you output the data using fetch (mysqli_fetch_array). Instead of using SELECT * FROM `table` to display all the data, you modify the output of the query. This will modify the SQL output so you don't need to use a PHP function to modify. Try looking for a query statement in your code and post the content. – Jm Verastigue Jun 27 '13 at 05:48
But insted of changing the creation column, do you have a piece of code to give me, to add to the code that I gave in my first post? I'm actually getting lost in all your answer... but thanks for your time! – Jun 27 '13 at 05:56

score 2 · Answer 2 · answered Sep 12 '13 at 09:54

I liked Jm Verastigue and tweaked it a little further for enhancements of dates within the current year vs dates outside the current year and also only worked on utc_time, but the now() approach is also valid.

SELECT 
report_time,
convert(CASE
    WHEN
        report_time between date_sub(utc_timestamp(),
            INTERVAL 60 minute) and utc_timestamp()
    THEN
        concat(minute(TIMEDIFF(utc_timestamp(), report_time)),
                ' minutes ago')
    WHEN datediff(utc_timestamp(), report_time) = 1 THEN 'Yesterday'
    WHEN
        report_time between date_sub(utc_timestamp(), INTERVAL 24 hour) and utc_timestamp()
    THEN
        concat(hour(TIMEDIFF(utc_timestamp(), report_time)),
                ' hours ago')
    WHEN
        year(report_time) >= year(utc_timestamp())
    THEN
        date_format(report_time, '%e %b')
    ELSE date_format(report_time, '%Y-%m-%d')
END using utf8) as `LekkerDate`

FROM
        _smry_prodinfo
    group by `LekkerDate`;

score 0 · Answer 3 · edited May 23 '17 at 12:22

0

You can do what you request via PHP (called "time ago").

Here's a PHP plugin on github, php-time-ago
Here's a relevant SO question - try the time_elapsed_string() and time_ago() functions
There's also a jQuery plugin, if you wish to do the transformation on the client side, timeago

edited May 23 '17 at 12:22

Community

1
1

answered Jun 27 '13 at 05:32

Filippos Karapetis

4,367
21
39

Thanks for your answer. I still have 2 problems. I have tried the first exemple of github. First, it give me this error: Class 'TimeAgo' not found.... Also, how can I put the date of my database insted of the date they give in the exemple? – Jun 27 '13 at 05:38
Have you included timeago.inc.php in your code? As for your date... it's the second parameter of the dateDifference() function, after a quick check of the code – Filippos Karapetis Jun 27 '13 at 05:49

How to get timestamp of MySQL table in relative time on website

3 Answers3