getting time difference from current time using sql database in php

Question

I am currently making a posting-comments system.

When a user comments on a post I want to store the date and time in a database. The existing comments should be displayd with time difference for how long has passed since the comment was added.

Ie.: 2minutes ago, 3 days ago, etc - similar to how it is on Facebook.

Is there any method or property in SQL or PHP to handle this?

Which SQL datatype would be suitable for this purpose? Datetime or Timestamp?

You want to use DATEDIFF(), plenty of solid examples on this site. — Hart CO, Jun 18 '13 at 17:52
Probably timestamp is best for you. Look here: [Converting timestamp to time ago in PHP](http://stackoverflow.com/questions/1416697/converting-timestamp-to-time-ago-in-php-e-g-1-day-ago-2-days-ago) — cssyphus, Jun 18 '13 at 17:54

geryjuhasz · Accepted Answer · 2013-06-18T17:58:56.877

Timestamps in MySQL generally used to track changes to records, and are often updated every time the record is changed. If you want to store a specific value you should use a datetime field. If you meant that you want to decide between using a UNIX timestamp or a native MySQL datetime field, go with the native format. You can do calculations within MySQL that way ("SELECT DATE_ADD(my_datetime, INTERVAL 1 DAY)") and it is simple to change the format of the value to a UNIX timestamp ("SELECT UNIX_TIMESTAMP(my_datetime)") when you query the record if you want to operate on it with PHP. An important difference is that DATETIME represents a date (as found in a calendar) and a time (as can be observed on a wall clock), while TIMESTAMP represents a well defined point in time. This could be very important if your application handles time zones. How long ago was '2010-09-01 16:31:00'? It depends on what timezone you're in. For me it was just a few seconds ago, for you it may represent a time in the future. If I say 1283351460 seconds since '1970-01-01 00:00:00 UTC', you know exactly what point in time I talk about.

For difference use:

$timeFirst  = strtotime($postDateTime);
$timeSecond = strtotime($commentDateTime);
$differenceInSeconds = $timeSecond - $timeFirst;

For displaying the time use this:

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

It will display exactly like on face. You have to pass the diff between comment and post in seconds to the function.

Thnx @geryjuhasz bro. Thats wat the answer i was luking for – Lokesh Devnani Jun 18 '13 at 18:16 — Lokesh Devnani, Jun 18 '13 at 18:16

score 0 · Answer 2 · answered Jan 15 '16 at 03:51

I just want to improve Jun's answer. after I tried to implement this on my code, Jun's have a little improper calculation on his code, in month and year calculation

here's my code

function relativedate($secs) {
    $second = 1;
    $minute = 60;
    $hour = 60*60;
    $day = 60*60*24;
    $week = 60*60*24*7;
    $month = 60*60*24*7*4;         // because 1 month is 4 week
    $year = 60*60*24*7*4*12;       // because 1 year is 12 month

    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

getting time difference from current time using sql database in php

2 Answers2