MySQL: How many minutes ago was DB updated?

Question

I need to keep a field in a data-base and update it with a time somehow, then later I need to check that time to see if it was over 30 minutes ago or not, and if not, how minutes left until 30?

I am going to be doing this with PHP+MySql can anyone tell me the simplest way to do this?

Thanks!!

Answer 1

I could wrap all you insert and update MySql calls in a function something like the following:

function MySqlQuery($query, $res){
    $result = mysql_query($qs, $res);
    if($result === false){
        mysql_query("QUERY STRING TO UPDATE FIELD IN DATABASE WITH NEW TIME", $res);
    }
    return $result;
}

Replace the "QUERY STRING TO UPDATE FIELD IN DATABASE WITH NEW TIME" with an actual update query and you should be good to go.

Answer 2

What I do is, put a Time Stamp on the latest record. Pull the latest record with a MySQL Query and then use the mysql fetch array function to get the time of that last record. This goes the same for using a database that is updated with the time only.

You would be able to manipulate that time with a function that compares the current time to the time on the record. From there you can display the time since last posting, and if it is over 30 minutes you can make it echo a message.

$currenttime = /* PHP Time Formatting you wish to use. */

$query = mysql_query("SELECT time FROM database");
$row = mysql_fetch_array($query);

echo "Last Record Posted @" . $row['time'];

$timesince = $currenttime - $row['time'];
echo "Time Since Last Post:" . $time - $row['time'];

if($timesince >= "30"){
echo "Over 30 Minutes!";
}

Let me know if you have any questions. The above code should give you an idea of how it would work, but it is a rough example.

Best of Luck!!

EDIT:::

Sorry, I misread the question, You would still need to enter the time into the database. You can still use the above code to pull the time and see if it is greater than 30 minutes or not.

For the Time Stamp check out the PHP Time Manual. You will want to pick the same time format for both the MySQL Input and the code I posted above.

Answer 3

Let's assume you want to know how long ago the last update/insert in the table occurred.
You can set up a table with a timestamp field with an on update clause

CREATE TABLE foo (
  id int auto_increment,
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  primary key(id),
  key(ts)
)

and then query the record with the largest value in ts

SELECT
  TIMEDIFF(Now()-Interval 30 Minute, ts)
FROM 
  foo
ORDER BY
  ts DESC
LIMIT
  1

edit: This also works if you want to get all records that have been inserted/modified within e.g. the last 12 hours.

SELECT
  TIMEDIFF(Now()-Interval 30 Minute, ts)
FROM 
  foo
WHERE
  ts > Now()-Interval 12 hour
ORDER BY
  ts DESC

edit2: there's also an off chance you might be interested in http://dev.mysql.com/doc/refman/5.1/en/show-table-status.html:

SHOW TABLE STATUS returns the following fields:
...
Update_time
When the data file was last updated. For some storage engines, this value is NULL. For example, InnoDB stores multiple tables in its tablespace and the data file timestamp does not apply. For MyISAM, the data file timestamp is used; however, on Windows the timestamp is not updated by updates so the value is inaccurate.