4

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

JeroenM
  • 807
  • 1
  • 11
  • 26

2 Answers2

6

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

Community
  • 1
  • 1
Sebastian Brosch
  • 42,106
  • 15
  • 72
  • 87
  • This answer worked for me, I only had to change MINUTES to MINUTE. Maybe MINUTES is for an older mysql version? – JeroenM Mar 16 '17 at 10:52
  • @JeroenM - thats right, my bad. I added also a simple timestamp solution. – Sebastian Brosch Mar 16 '17 at 11:09
  • Thanks! Why would one use the timestamp solution if it could cause problems, even though 2038 is still far away? – JeroenM Mar 16 '17 at 12:40
  • 1
    At the moment it is working and should not cause any problems. This would be a problem in 21 years (a long time). A very very good explanation what is happening in 2038: http://stackoverflow.com/a/2012620/3840840 – Sebastian Brosch Mar 16 '17 at 14:06
1

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
Anil
  • 3,722
  • 2
  • 24
  • 49
  • Thanks, this answer works aswell. I a few seconds after I accepted the other one, you update your answer with a nice explaination. The table set might indeed become large in the long run, so that is why this answer is a little bit more usefull for me than the other, though appreciate Sebastian's answer aswell. Thanks both of you (: – JeroenM Mar 16 '17 at 10:57
  • @Anril - It is hard to believe that converting `DATETIME` to `TIMESTAMP` and calculating after is faster than `DATE_SUB`. See the following answer on so: http://stackoverflow.com/a/35469149/3840840. Can you show some benchmarks confirm this? - and this solution is only working until 2038! `DATE_SUB` works until 9999. – Sebastian Brosch Mar 16 '17 at 11:29
  • @SebastianBrosch, I was going with perception (based on experience also)" avoid function" in query, you have a strong point about Unix Millennium problem, your answer is more suitable then me no doubt now +1 from me. – Anil Mar 16 '17 at 11:45
  • @Anil - thx ;) - I don't want to call your solution as bad solution but it is not ready for the future. It is working for now and it is working in 10 years that's not the problem. And until 2038 there is a long way :D – Sebastian Brosch Mar 16 '17 at 11:49
  • @JeroenM, you should accept Sebastian's answer, will use also. Mine is not future ready. I am not deleting my answer for the very valuable comment about "Unix Millennium problem" from Sebastian. – Anil Mar 16 '17 at 11:57