PHP - Calculate time between dates

Question

Hello, would like to learn how I can calculate the time between two dates. I am working on an Unlock user functionality and the following is what I have:

// get offender's username from session  
$username = $_SESSION['UserName'];  
require_once('./connections/mysql.php');  

$checklockoutexpiration = mysqli_query($conn,"SELECT IsLockedOut, LastLockoutDate FROM users WHERE UserName = '$username' Limit 1") or die($dataaccess_error);  

if(mysqli_num_rows($checklockoutexpiration) == 1)  
{  
    $row = mysqli_fetch_array($checklockoutexpiration);  
    $islockedout = $row['IsLockedOut'];  
    $lastlockoutdate = $row['LastLockoutDate'];  
}  

if(**$islockedout == 1 && $lastlockoutdate + LOCKOUT_DURATION_IN_MINUTES < getdate()**)  
{  
    // unlock user  
}  

The lockout date is in the database in a form of NOW() and looks like 2010-10-13 13:01:05. I assume it is server time which at the moment is my local development machine.

QUESTION: How can I do the bold line in the code.

In asp.net I would do something like:

// if lockout datetime + lockout duration is less than datetime now  
if (usrInfo != null && usrInfo.IsLockedOut && usrInfo.LastLockoutDate.ToUniversalTime().AddMinutes(passwordAttemptLockoutDuration) < DateTime.UtcNow)  
{  
// then automatically Unlock user  
    usrInfo.UnlockUser();  
}  

Could some one please show me how this is done? Thank you!

Answer 1

Short answer: Convert your timestamp and lockout duration to seconds, then compare.

Long answer:

$lockout = ($lockoutDurationMinutes * 60);
$timestamp= strtotime($yourTimeStampFieldVariable);

if ( ($timestamp + $lockout) > time())
{
// still locked out
}

Answer 2

Use http://php.net/strtotime to turn a date of "2010-10-13 13:01:00" format into seconds since epoch as a integer.

Use http://php.net/time to get current seconds since epoch as a integer.

Then it's easy.

Answer 3

If you want to do it more like your ASP example, use the DateTime class. You should be able to construct one from your string, and, along with its counterpart DateInterval class, you can add an interval to a time or use diff to get an interval from two times.

(Looks like this is only available on PHP 5.3 though. That might be a deal breaker.)

Answer 4

There are already acceptable answers... so I'll show a different approach. I'd change your querry string to:

SELECT IsLockedOut, LastLockoutDate, 
    (addtime(LastLockoutDate, $passwordAttemptLockoutDuration) < NOW()) unlocked 
FROM users WHERE UserName = '$username' Limit 1

Where $passwordAttemptLockoutDuration a string of the form "hours:minutes:seconds"

The value "unlocked" will be 0 (or false) if they're still locked, and it will be 1 if they can log in again.

Answer 5

Thank you for all the advice. I did end up solving the problem by adding a new column (LastUnlockDate). When the user gets locked out, I set the IsLockedOut to 1, the LastLockoutDate to NOW() and the LastUnlockDate to NOW() + $lockoutduration (in seconds).

Then part of my code looks something like:

//------------------------------------------------------------
// if lockout session exits
//------------------------------------------------------------

if(!empty($_SESSION['count'])) {
require_once('./connections/mysql.php');
require_once('./web.config.php');
require_once('./controls/login/error_messages.php');

$count = $_SESSION['count'];
$offender = mysqli_real_escape_string($conn, $_SESSION['offender']);
$maxloginattempt = MAX_LOGIN_ATTEMPT;

if($count >= $maxloginattempt)
{
    echo $maxloginattempt_error;
    $showform = 0;

    // check if user exists in database
    $checkoffender = mysqli_query($conn, "SELECT UserName, IsLockedOut, LastLockoutDate, LastUnlockDate, NOW() AS ServerTime FROM users WHERE UserName = '$offender' LIMIT 1")
    or die($dataaccess_error);

    if(mysqli_num_rows($checkoffender) == 1)
    {
        $row = mysqli_fetch_array($checkoffender);
        $islockedout = $row['IsLockedOut'];
        $lastlockoutdate = $row['LastLockoutDate'];
        $lastunlockdate = $row['LastUnlockDate'];
        $servertime = $row['ServerTime'];
    }

    // unlock user if lockout has expired
    if($islockedout == 1 && $lastunlockdate < $servertime)
    {
        $unlockoffender = mysqli_query($conn,"UPDATE users SET IsLockedOut = 0 WHERE UserName = '$offender'") 
        or die($updateactivity_error);

        unset($_SESSION['count']);
        unset($_SESSION['offender']);

        $showform == 1;
    }

    // otherwise lockout the user
    if($islockedout == 0)
    {
        $lockoutduration = (LOCKOUT_DURATION * 60);
        $updateoffender = mysqli_query($conn,"UPDATE users SET IsLockedOut = 1, LastLockoutDate = NOW(), LastUnlockDate = (NOW() + $lockoutduration)  WHERE UserName = '$offender'") 
        or die($updateactivity_error);
    }
}

}

Because this code purely relies on the presence of a session, if the browser is closed and the session is lost, the user in the database is still set as locked out. So, to fix that, I modified my actual login page which now checks during authentication and unlocks the user if the lockout has already expired.

Thank you all for all the help and suggestion. So to wrap up, my question was, how can I add time to the date - and the simple answer was: NOW() + $lockoutduration.