time elapsed with php mysql database

Question

Im working on my review page and I have this this simple question.
how do I change my database result, into "[result x] days/month/years ago"? it seems like curent date minus(-) post date. but, how do I write it corectly.

thanks for your help.

here is my code,

<?php
$sql=mysql_query("SELECT * FROM reviews ORDER BY review_code DESC LIMIT 3");
while($db=mysql_fetch_array($sql)){

echo "
<div style='padding-bottom: 25px;'>
Post Date: ".$db['post_date']." minus(-) Current Time: ".time()."
</div>
";}
?>

the result is

Post Date: 2017-06-27 08:00:00 minus(-) Current Time: 1498701679

can anyone help me? please..

Do you mean that you have a date value and you want to translate it into a "time ago" format, similar to how Stack Overflow lists times of questions/comments/etc.? A Google search for something like "javascript time ago" will get you started with that. There are small libraries which convert your display values for you. — David
– David, Commented Jun 29, 2017 at 2:21
Yes.. I did this concept on my javascript. and it works very well, because it doesn't contain any database. it results "a time ago" completly like I want. but, in this case, I'm working with php and database. which is a new things for me. and I dont really understand how to do it. — Dwipa Arantha
– Dwipa Arantha, Commented Jun 29, 2017 at 2:27
Is your PHP code not outputting to a web page? If so (as most PHP does), then you can use the JavaScript library. — David
– David, Commented Jun 29, 2017 at 2:28
the output shows the right data. did you mean that ".$db['post_date']." can be convert using javascript? — Dwipa Arantha
– Dwipa Arantha, Commented Jun 29, 2017 at 2:32
I mean that JavaScript doesn't care where the data comes from, it's just going to convert whatever value you give it. You can hard-code the value for testing, or output it from a database. To JavaScript it's literally the same thing. — David
– David, Commented Jun 29, 2017 at 2:35

user2463 · Accepted Answer · 2017-06-29 02:27:33Z

1

Take a look here: http://php.net/manual/en/datetime.diff.php

What you should basically do is create two datetime objects and use the diff function:

$datetime1 = new DateTime($db['post_date']); // Date post was created
$datetime2 = new DateTime(); // Default DateTime is now
$interval = $datetime1->diff($datetime2);
echo $interval->format('Post created %a days ago');

If you want to dynamically change wording (days, months, years, etc) check out this comment: http://php.net/manual/en/datetime.diff.php#97880

answered Jun 29, 2017 at 2:27

user2463

7524 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dwipa Arantha Over a year ago

it works, but when i changes the post database into 2015, it shows "Post created 547 days ago" how do you convert that into "months ago" and "years ago"? and 1 more question, can we have "if 0 days = today" and "if 1 days = yesterday"?

Atli · Accepted Answer · 2017-06-29 02:26:45Z

0

There is no built in functionality to do this. You'd have to write the code manually, to find the diff between now and the date, and convert that in to time units.

However you don't have to re-invent the wheel here. Date manipulation libraries like Moment already implement features for this. (Look at the Moment::calendar() method)

answered Jun 29, 2017 at 2:26

Atli

7,9492 gold badges34 silver badges44 bronze badges

3 Comments

user2463 Over a year ago

If you're gonna use a library, just go with the more popular and widely-used Carbon ;) carbon.nesbot.com/docs

Atli Over a year ago

@theomessin Sure, that's another way to go. I just always preferred this Moment library, because it's essentially the same as it's JavaScript counterpart, and I like the symmetry :)

user2463 Over a year ago

Fair enough. Just wanted to start a lib war cause I've got nothing better to do right now.. XD

Cogwizzle · Accepted Answer · 2017-06-29 02:28:14Z

Dwipa good news. ~~PHP has already got functions that do just that.~~ The time_elapsed_B function should do what you want. Here is the article with the information in it. http://php.net/manual/en/function.time.php

EDIT Whoops not built in but provided at link.

function time_elapsed_A($secs){
    $bit = array(
        'y' => $secs / 31556926 % 12,
        'w' => $secs / 604800 % 52,
        'd' => $secs / 86400 % 7,
        'h' => $secs / 3600 % 24,
        'm' => $secs / 60 % 60,
        's' => $secs % 60
    );

    foreach($bit as $k => $v)
        if($v > 0)$ret[] = $v . $k;

    return join(' ', $ret);
}


function time_elapsed_B($secs){
    $bit = array(
        ' year'        => $secs / 31556926 % 12,
        ' week'        => $secs / 604800 % 52,
        ' day'        => $secs / 86400 % 7,
        ' hour'        => $secs / 3600 % 24,
        ' minute'    => $secs / 60 % 60,
        ' second'    => $secs % 60
    );

    foreach($bit as $k => $v){
        if($v > 1)$ret[] = $v . $k . 's';
        if($v == 1)$ret[] = $v . $k;
    }
    array_splice($ret, count($ret)-1, 0, 'and');
    $ret[] = 'ago.';

    return join(' ', $ret);
}

fnocetti · Accepted Answer · 2017-06-29 02:29:53Z

The following function was provided by the community at http://php.net/manual/en/function.time.php. It returns a human readable representation of a number of seconds. So feed it with the difference in seconds between now and your date.

function time_elapsed_B($secs){
    $bit = array(
        ' year'        => $secs / 31556926 % 12,
        ' week'        => $secs / 604800 % 52,
        ' day'        => $secs / 86400 % 7,
        ' hour'        => $secs / 3600 % 24,
        ' minute'    => $secs / 60 % 60,
        ' second'    => $secs % 60
        );

    foreach($bit as $k => $v){
        if($v > 1)$ret[] = $v . $k . 's';
        if($v == 1)$ret[] = $v . $k;
        }
    array_splice($ret, count($ret)-1, 0, 'and');
    $ret[] = 'ago.';

    return join(' ', $ret);
}

Example use:

$seconds = strtodate($db['post_date']);

echo time_elapsed_B(time() - $seconds);

Collectives™ on Stack Overflow

time elapsed with php mysql database

4 Answers 4

1 Comment

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related