UPDATE column where timediff is greater than 5 minutes

Question

I have a table (sessions) which has 2 columns that I have to use for this query. Session_Active (which is a tinyInt) and Last_active(which is a datetime).

I want to create a query that calculates the time difference between now and 'Last_active' for all tables WHERE 'Session_Active' is true, and if its greater than 5 minutes it should change 'Session_Active'.

This is the part that I have which works:

SELECT timediff(now(), `Last_Active`) from sessions WHERE `Session_Active` = true;

I have no clue at all how I can check if the difference is greater than 5 minutes, neither do I know where/how to put the UPDATE Session_Active = false (If the difference is 5 minutes or more)

Thanks in advance! (:

Answer 1

You can use the following solution using DATE_SUB:

UPDATE sessions SET `Session_Active` = 0
WHERE `Last_Active` <= DATE_SUB(NOW(), INTERVAL 5 MINUTE)
    AND `Session_Active` = 1

You want to use a timestamp solution?

You can use TIMESTAMPDIFF:

UPDATE sessions SET `Session_Active` = 0
WHERE TIMESTAMPDIFF(MINUTE, `Last_Active`, NOW()) >= 5
    AND `Session_Active` = 1

Note: You should be careful with using TIMESTAMP! Some information why you shouldn't use TIMESTAMP: https://stackoverflow.com/a/35469149/3840840. On this answer there is a reference to this article describing the performance of DATETIME, TIMESTAMP and INT.

The TIMESTAMP solution is only working until 2038. This will be caused by the Year 2038 problem.

A very good explanation of this problem and what is happening in 2038: https://stackoverflow.com/a/2012620/3840840