Query to test and return timed lockout in MySQL

Question

I have some functionality in my webapp that locks out users from certain behavior for a set amount of time (e.g., they have to wait 10 minutes before they can update a level).

My query looks like this:

SELECT date_last_updated FROM levels
WHERE user_id = 123 AND date_last_updated > date_sub(now(), INTERVAL 10 MINUTE);`

Please note that date_last_updated is DATETIME.

Currently, if a row is returned I know they're still in the lockout, so that's fine for a boolean result. But just a single column row of the remaining time, in minutes as an integer would be better. That is where anything> 0 indicates both lockout is in effect, and how much time is left.

How would I do this?

Answer 1

I think a more elegant solution is

SELECT 
TIMESTAMPDIFF(MINUTE, date_last_updated, now()) as mins_since_last_update
FROM levels
WHERE id = 123

This gives me the time value as the OP requested, as an integer.

Answer 2

You should use the both the UNIX_TIMESTAMP and SEC_TO_TIME functions

SELECT SEC_TO_TIME(UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(date_last_updated)) LockoutTimeLeft
FROM levels WHERE user_id = 123
AND date_last_updated > date_sub(now(), INTERVAL 10 MINUTE);

Here is the idea:

• Subtract date_last_updated in Seconds from NOW() in Seconds
• Convert Difference into the Time Format HH:MM:SS

If you want, you can change the format thereafter.

UPDATE 2014年02月26日 16:06 EST

Read your last comment

Just subtract the difference from 10 min (600 sec)

SELECT
SEC_TO_TIME(600 + UNIX_TIMESTAMP(date_last_updated) - UNIX_TIMESTAMP(NOW())) LockoutTimeLeft
FROM levels WHERE user_id = 123
AND date_last_updated > date_sub(now(), INTERVAL 10 MINUTE);

or perhaps

SELECT
SEC_TO_TIME(600 + UNIX_TIMESTAMP(date_last_updated) - UNIX_TIMESTAMP(NOW())) LockoutTimeLeft
FROM levels WHERE user_id = 123
AND date_last_updated > now();

Give it a Try !!!

• Hal50000: I tried this but the LockoutTimeLeft is only the amount of time elapsed since date_last_updated, and it doesn't give me the time remaining in the lockout as an integer. Changing the value for the INTERVAL has no effect since it's time elapsed, not time remaining. The only difference is once it reaches the value in INTERVAL, no rows are returned. That was sort of my original problem.
• Hal50000: Then don't I need to change the time value in two places?