Datetime comparison PHP/Mysql?

Question

I'm trying to make something like this:

if (datetime - system date > 15 minutes) (false)

if (datetime - system date <= 15 minutes) (true)

But I'm totally lost. I don't know how to make this operation in PHP.

I'd like to see how I can pick that DateTime from my database and check if it's between the last 15 minutes of my server's time.

The type of database is MySQL.

---

Finally, I managed to do it thanks to Sammitch and the other ones, here i leave the snippet:

$now    = time();
$target = strtotime($row[1]);
$diff   = $now - $target;

// 15 minutes = 15*60 seconds = 900
if ($diff <= 900) {
    $seconds = $diff;
} else {
    $seconds = $diff;
}

Solution 1

If your dates are already in MySQL you will want to do the comparison in the query because:

1. MySQL has proper DATE types.
2. MySQL has indexes for comparison.
3. MySQL performs comparisons much faster than PHP.
4. If you filter your data in the query then less, or no time is spent transferring superfluous data back to the application.

Below is the most efficient form. If there is an index on the date column it will be used.

SELECT *
FROM table
WHERE date > DATE_SUB(NOW(), INTERVAL 15 MINUTE)

Docs: DATE_SUB()

If you need to do it in PHP:

$now = time();
$target = strtotime($date_from_db);
$diff = $now - $target;
if ( $diff > 900 ) {
  // something
}

or, more succinctly:

if( time() - strtotime($date_from_db) > 900 ) {
  // something
}

Solution 2

You have a solution for MYSQL in other answers, a good solution for PHP is:-

$now = new \DateTime();
$target = new \DateTime(getTimeStringFromDB());
$minutes = ($target->getTimestamp() - $now->getTimestamp())/60;
if($minutes < 15){
    // Do some stuff
} else {
    //Do some other stuff
}

Solution 3

the most efficient PHP/MySQL combined solution is:

$date = date('Y-m-d H:i:s', strtotime('-15 minutes'));
$data = $pdo->query("SELECT date_field > '$date' as expired from table")->fetchAll(PDO::FETCH_COLUMN);
foreach($data as $expired) {
    if (!$expired) {
        // Still Valid
    }
}

Solution 4

Try this

$interval = date_create($date_from_db)->diff(new \DateTime());
if ($interval->format('%r%l')>15) {
    $result = false;
} else {
    $result = true;
}

View more solutions

Comments

[Ralsho]

I can't do it in the query, i need to do it in my PHP, with my mysql datetime and my datetime with php...

[Ralsho]

Thanks for answering but it failed i get that error: FATAL ERROR: Call to undefined method DateTime::diff() I need to do something more?

[Ralsho]

Uncaught exception 'Exception' with message 'DateTime::__construct(): It is not safe to rely on the system's timezone settings. You are required to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected the timezone 'UTC' for now, but please set date.timezone to select your timezone

[Sammitch]

You either need to call date_default_timezone_set() as noted in the error message, or set date.timezone in your php.ini

[Ralsho]

i set date.timezone like that: [Date] ; Defines the default timezone used by the date functions ;date.timezone = "Europe/Amsterdam" But im still having the error

[vascowhite]

@Sammitch Your PHP method is flawed. If the difference is, for example, 1 hour and 10 minutes, then $diff will be 10, you would need to check that hours and days are 0 too. This isn't a criticism, just pointing out a mistake I have fallen foul of myself. It is a hard bug to find :)

[Sammitch]

@vascowhite you are correct, I've edited the answer with a better [working] solution. I try to use DateTime as much as possible, but some things [like this, for example] it is so ass-backwards that simple timestamp math is preferable.

[vascowhite]

@Sammitch Well you could just do if(($now->getTimestamp() - $target->getTimestamp())/60 > 15){//do something}. IMHO that is still better than resorting to strtotime() as the DateTime objects are still available for other calculations.

[Ralsho]

Thanks for answer but i don't get the getTimeStringFromDB, isn't working for me or i dunno how to use it, i don't see anything of that on PHP manual.

[vascowhite]

@Ralsho The getTimeStringFromDB() is a place holder as I don't know how you get your time string. You're supposed to replace it with the actual value. sigh

[sree]

what is 900 here?

[Lucas Basquerotto]

@sree The number of seconds in 15 minutes (15 * 60)

[Sammitch]

@BlueWater FYI: unix timestamps are UTC which does not have DST changes.

[Admin]

@Sammitch unix timestamps are UTC which does not have DST changes. Exactly. which is why direct calculations on numbers as seconds yields incorrect times. Also, please avoid statements such as "Below is the most efficient form". Such absolute statements are not prudent in the field of computer science. Generally, things are more efficient solutions based on various factors, which can change based on specific problems. There's almost always more than one good solution.