Adding value to Time Stamp

Question

Hi Guys,

I am trying to create a script for quiz. I have stored current time stamp in mysql DB as quiz start time, and want to check after each quiz how much time is left.

I have the idea that I will add 30 mins to saved time stamp and subtract this value from current time. That will be the time left. But I don't know the exact way of doing this.

My time stamp is saved in DB in fromat 2010-08-24 20:08:59. Any one have the idea.

Please let me know if someone have done it, or know how to get it.

Adding 30 mins to time stamp and showing the user how much time is left.

I am using the now() function to store the timestmp in DB.

Thanks

Answer 1

I would personally store the output of PHP time() in the database. If you a human readable format from this value, you could use date('Y-m-d H:i:s', $fromdatabase);.

Answer 2

With PHP you can just use strtotime('2010-08-24 20:08:59 +30 min') (see strtotime()).

But instead of saving dates in this format (by the way is this a datetime, text or varchar?) I would use unix timestamps (see time()), to which you can just add 1800 seconds (30 min). To get a human-readable date from that, use date().

Finally, in order to have a running timer displayed to the user, you'd need javascript.

Answer 3

You want to store an actual UNIX timestamp in the database, not a string in that format.

You may or may not be doing this already, it depends on the type of column you're using. For MySQL, you should be using TIMESTAMP, which allows you to retrieve the timestamp with

SELECT UNIX_TIMESTAMP(column_name) ...

To store the current time + 30 minutes, all you have to do is:

INSERT INTO table (column_name) VALUES(UNIX_TIMESTAMP() + 1800)

You can know if the time has expired by comparing time() against the value of the column.