如何更新超过30秒或30分钟的行[重复]

Question

This question already has an answer here:

• Counting rows where a timestamp is less than 24 hours old 4 answers

I'm trying to figure out how to update MySQL rows older than 30 seconds or 30 minutes.

$sql = "SELECT C.sessid, C.datetime, U.username, U.firstname, U.lastname, U.sessid FROM checkins C, users U WHERE U.sessid = C.sessid AND SUBTIME(NOW(),'0 00:01:00')>C.datetime";
$result = mysql_query( $sql );
while( $row = mysql_fetch_array( $result, MYSQL_ASSOC ) ) {
mysql_query("UPDATE checkins SET status = 'was in'");
}

Here I have joined tables, just the users table and checkins table. I am using subtime to determine the time difference and trying to update rows of checkins table if the C.datetime is older than 1 minute just for a test

</div>

Answer 1

Is this what you are looking for?

UPDATE checkins c join
       users u
       on c.sessid = u.sessid
    SET status = 'was in'
    WHERE c.datetime < now() - interval 30 second;

The users table is probably not needed, unless you require it for filtering.

Just change the 30 second to 30 minute for a 30 minute interval.