CODEWITHSUNDEEP
mysqlsession
JeroenM
JeroenM

Reputation: 837

UPDATE column where timediff is greater than 5 minutes

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! (:

Upvotes: 4

Views: 5647

Answers (2)

Sebastian Brosch
Sebastian Brosch

Reputation: 43574

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

Upvotes: 7

Anil
Anil

Reputation: 3752

You can use UNIX_TIMESTAMP(date)

When UNIX_TIMESTAMP() is called with a date argument, it returns the value of the argument as seconds since '1970-01-01 00:00:00' UTC. The date argument may be a DATE, DATETIME, or TIMESTAMP string, or a number in YYMMDD, YYMMDDHHMMSS, YYYYMMDD, or YYYYMMDDHHMMSS format. The server interprets date as a value in the current time zone and converts it to an internal value in UTC. This is faster then DATE_SUB on large table set.

UPDATE sessions 
 SET `Session_Active` = 0
  WHERE UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(`Last_Active`) > 300
  AND `Session_Active` = 1

Upvotes: 1

Related Questions