MySQL and PHP counting the minutes and hours from registration date?

Question

How do I count the minutes and hours since a member registered from there registration date that is stored in my MySQL database as datetime using PHP & MySQL.

Here is what I have so far.

date('F j, Y g:i:s A', strtotime($row['rdate']))

Answer 1

The cleanest answer is to ask the database SQL parser to do it.

SELECT
   DATEDIFF(NOW(), registered_date) AS days_since_registration,
   TIMEDIFF(NOW(), registered_date) AS hours_since_registration
FROM users;

That's assuming the column registered_date is of format DATETIME.

BTW, it's also better to use UNIX_TIMESTAMP() on the query rather than using strftime() as MySQL has an internal format for the datetime and can convert it to a unix time without guessing. strftime() is unlikely to have problems but does have to parse a date format and that involves guessing.

Answer 2

It looks like you almost had it, this gives the basic principle:

$seconds = time() - strtotime( $row['rdate'] );
$minutes = $seconds / 60;
$hours = $seconds / 3600;
$days = $seconds / 86400;
$weeks = $seconds / 604800;

Answer 3

time ago in words taken from examples on here http://php.net/manual/en/function.time.php

is that what you want

example output 1 hour 3 minutes ago

public static function timeAgo($date){
            if(empty($date)) {
                return "No date provided";
            }

            if( Settings::read( 'localize') ){
                $periods = array("{{second}}", "{{minute}}", "{{hour}}", "{{day}}", "{{week}}", "{{month}}", "{{year}}", "{{decade}}");
            }else{
                $periods = array("second", "minute", "hour", "day", "week", "month", "year", "decade");
            }
            $lengths = array("60","60","24","7","4.35","12","10");

            $now = time();

            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}";
        }