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.

Adam Azad · Accepted Answer · 2018-06-27 15:28:03Z

54

First convert $timest to timestamp

$time = strtotime($timest);

$curtime = time();

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

edited Jun 27, 2018 at 15:28

Adam Azad

11.3k5 gold badges31 silver badges74 bronze badges

answered Feb 1, 2013 at 13:35

William Buttlicker

6,0005 gold badges35 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Josh Boothe Over a year ago

Is the 1800 seconds, mins or hours?

Dave Over a year ago

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 · Accepted Answer · 2013-02-01 13:33:52Z

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 1, 2013 at 13:33

Dave

3,2882 gold badges25 silver badges40 bronze badges

Comments

Vlad Preda · Accepted Answer · 2013-02-01 13:33:09Z

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.

Gleb Kemarsky · Accepted Answer · 2017-08-16 09:39:44Z

-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, 2017 at 9:39

Gleb Kemarsky

10.5k7 gold badges49 silver badges73 bronze badges

answered Feb 1, 2013 at 13:29

Andrej Bestuzhev

6746 silver badges10 bronze badges

Collectives™ on Stack Overflow

Comparing timestamp to current time from database

4 Answers 4

2 Comments

Comments

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

1 Comment

Comments

Linked

Related