How to calculate time left in a count down scenario

Question

We have a requirement to count down based on a user taking a test. What would be a best way to tackle tracking the time taken by a user while taking the test.

We do capture start time, end time. But the calculations go awry if the application server or the OS goes down during the test. We were thinking of using another variable to store the current time after the user submits an answer to the question. So (end time - current time) would reasonably account for the amount of time left.

Is there an effective way to calculate the "time left" in such cases other than the one mentioned above?

We would like the solution to be database agnostic as possible

Answer 1

To be specific, I'll continue with MYSQL.

As you may stated, you have captured start time. When the test loaded by the user, write this timestamp in a DATETIME field. Another option is that using UNIX_STAMP. And then, when user submits the answer, you may easily put this data to another DATETIME field.

As well as other rdbms systems, mysql got the date-time manipulation functions.

SELECT CURRENT_TIMESTAMP(); query returns current timestamp. eg. '2007-12-15 23:50:26'

SELECT UNIX_TIMESTAMP(); query returns current unix timestamp which may be easy to calculate difference. eg. 1111885200

Also we have got DATE_SUB() and DATE_ADD() functions for addition, subtraction operations.

Please visit date-time manual page for details. I guess this information will lead you to a proper solution.

-- Added on Sep 18:

You may use javascript to track user behaviour. For instance, a function calls a server side script with a salt or something you have in session. That server side script records the current timestamp as "last update". Database parts same as above.