Calculates difference between two dates in PHP

Question

HI, i have a couple of posts in my MySql database server, one of the info content in each post is the date and time in the format datetime (Ex. 2010-11-26 21:55:09) when the post was made.

So, i want to retrive the actual date and time from the SQL server with the function NOW() and calculates how many seconds or minutes or hours or days ago was post the info.

I dont know how to create this php script but i know that for sure is allready made, so thanks for any help.

Answer 1

As billythekid said, you can use the date_diff() function if you are using PHP5.3+, if you are not then there are various methods. As shown by other posters. The quickest method in MySQL if you want to know the time split in to the "hours:mins:secs" hierarchy is to use the TIMEDIFF() function.

SELECT TIMEDIFF(NOW(), '2010-11-26 12:00:00');

If you want it as seconds, use the unix timestamp features in MySQL or in PHP, you can convert MySQL dates to PHP quickly using strtotime().

Answer 2

you could use the date_diff() function

http://php.net/manual/en/function.date-diff.php

Something like...

<?php 
$now = time();
$then = $posttime;
$diff = date_diff($now,$then);
echo $diff->format('%R%d days'); #change format for different timescales
?>

edit --

I actually solve this issue on one of my twitter apps using this function...

function time_since ( $start )
{
    $end = time();
    $diff = $end - $start;
    $days = floor ( $diff/86400 ); //calculate the days
    $diff = $diff - ($days*86400); // subtract the days
    $hours = floor ( $diff/3600 ); // calculate the hours
    $diff = $diff - ($hours*3600); // subtract the hours
    $mins = floor ( $diff/60 ); // calculate the minutes
    $diff = $diff - ($mins*60); // subtract the mins
    $secs = $diff; // what's left is the seconds;
    if ($secs!=0) 
    {
        $secs .= " seconds";
        if ($secs=="1 seconds") $secs = "1 second"; 
    }
    else $secs = '';
    if ($mins!=0) 
    {
        $mins .= " mins ";
        if ($mins=="1 mins ") $mins = "1 min "; 
        $secs = '';
    }
    else $mins = '';
    if ($hours!=0) 
    {
        $hours .= " hours ";
        if ($hours=="1 hours ") $hours = "1 hour ";             
        $secs = '';
    }
    else $hours = '';
    if ($days!=0) 
    {
        $days .= " days "; 
        if ($days=="1 days ") $days = "1 day ";                 
        $mins = '';
        $secs = '';
        if ($days == "-1 days ") {
            $days = $hours = $mins = '';
            $secs = "less than 10 seconds";
        }
    }
    else $days = '';
    return "$days $hours $mins $secs ago";
}

You pass it in a unix timestamp of the time to check (the post time) and it returns the various string.

Answer 3

Usually, you do this kind of thing in a query, but MySQL isn't very good with intervals (it would be very easy with PostgreSQL). You could convert it to unix timestamp, then it would give the number of seconds between the two dates :

SELECT UNIX_TIMESTAMP() - UNIX_TIMESTAMP(your_datetime_column);

I thought about DATEDIFF, but it only returns the number of days between the two dates.

You can do it in PHP, for instance, with DateTime class :

$date1 = new DateTime();
$date2 = new Datetime('2010-11-26 12:00:00');

var_dump($date1->diff($date2));

(There's a procedural way to do this, if you're not a fan of OOP.)
This is definitely the solution I'd use if I can't do it with the RDBMS. DateTime::diff returns a DateInterval object, which contains the number of seconds, minutes, hours, days, etc. between the two dates.

You could also do it with timestamps in PHP :

$num_sec = time() - strtotime('2010-11-26 12:00:00');

Which would return the same thing as the SQL query.