Comparing timestamp to current time from database

Question

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.

score 54 · Accepted Answer · edited Jun 27 '18 at 15:28

54

First convert $timest to timestamp

$time = strtotime($timest);

$curtime = time();

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

edited Jun 27 '18 at 15:28

Adam Azad

11,171
5
29
70

answered Feb 01 '13 at 13:35

Engineer

5,911
4
31
58

Is the 1800 seconds, mins or hours? – Josh Boothe Feb 01 '13 at 14:22
3

seconds but all this multiple converting of dates is a waste of time/resources when you can do it all in query and just get back a list of ID's you need to process. – Dave Feb 01 '13 at 14:37

score 13 · Answer 2 · answered Feb 01 '13 at 13:33

13

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.

answered Feb 01 '13 at 13:33

Dave

3,280
2
22
40

score 2 · Answer 3 · answered Feb 01 '13 at 13:33

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 !

FYI: You should **not use** this approach **in 2020** for SQL injection reasons. — Advena, Dec 02 '20 at 22:04

score -1 · Answer 4 · edited Aug 16 '17 at 09:39

-1

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.

edited Aug 16 '17 at 09:39

Gleb Kemarsky

10,160
7
43
68

answered Feb 01 '13 at 13:29

Andrej Bestuzhev

674
6
10

Comparing timestamp to current time from database

4 Answers4

Linked