CODEWITHSUNDEEP
php
Josh Boothe
Josh Boothe

Reputation: 1423

Comparing timestamp to current time from database

I need to test current time against a datetime from database, if it has been 30 mins then execute code, if not then dont. This is where I am at and I am stuck:

$link = mysqli_connect("hostname", "username", "password", "database");
$q = "SELECT id FROM dwCache ORDER BY id DESC LIMIT 1";
$qu = mysqli_query($link, $q);

while($row=mysqli_fetch_array($qu, MYSQL_ASSOC)){
        $id = $row['id'];
        $cache = $row['cache'];
        $timest = $row['time'];
    }

$newTime = 
$difference = $timest
if($timest >= )

As you can see towards the bottom I lose it as I am not sure what to do.

$timest returns : 2013-02-01 12:36:01 as the format Y-m-d h-i-s

Apologies on double post, other deleted.

Upvotes: 29

Views: 72642

Answers (4)

William Buttlicker
William Buttlicker

Reputation: 6000

First convert $timest to timestamp

$time = strtotime($timest);

$curtime = time();

if(($curtime-$time) > 1800) {     //1800 seconds
  //do stuff
}

Upvotes: 54

Andrej Bestuzhev
Andrej Bestuzhev

Reputation: 674

SELECT id FROM dwCache ORDER BY id DESC LIMIT 1

you'll get only id field, it's the first. The second, for time converting: MySQL convert datetime to Unix timestamp

Third: you can convert your time string using strtotime function.

Upvotes: -1

Dave
Dave

Reputation: 3288

do it all in sql statement 

SELECT id FROM `dqCache` WHERE `time`<DATE_SUB(NOW(), INTERVAL 30 MINUTE);

This will return everything from your table where the time column is before 30 minutes before now.

Upvotes: 13

Vlad Preda
Vlad Preda

Reputation: 9930

I like to use unix timestamps in this situation.

$timest = date('u'); // gets the unix timestamp
$q = "SELECT id 
      FROM `dwCache` 
      WHERE {$timest} - UNIX_TIMESTAMP(`timestamp_col`) > 1800";

Explanation:

This basically calculates the difference between the current time and the time in the table column. If it's higher than 1800 (30 minutes), it will select the row, and your PHP code will be executed.

Advantages

There are some advantages to using this instead of the PHP check you started doing. You will select fewer rows, thus occupy less memory.

PS:

Thumbs up for using MySQLi !

Upvotes: 2

Related Questions