I am storing the last login time in MySQL in, datetime
-type filed. When users logs in, I want to get the difference between the last login time and the current time (which I get using NOW()
).
How can I calculate it?
This question is related to
mysql
USE TIMESTAMPDIFF
MySQL function. For example, you can use:
SELECT TIMESTAMPDIFF(SECOND, '2012-06-06 13:13:55', '2012-06-06 15:20:18')
In your case, the third parameter of TIMSTAMPDIFF
function would be the current login time (NOW()
). Second parameter would be the last login time, which is already in the database.
If your start and end datetimes are on different days use TIMEDIFF.
SELECT TIMEDIFF(datetime1,datetime2)
if datetime1 > datetime2 then
SELECT TIMEDIFF("2019-02-20 23:46:00","2019-02-19 23:45:00")
gives: 24:01:00
and datetime1 < datetime2
SELECT TIMEDIFF("2019-02-19 23:45:00","2019-02-20 23:46:00")
gives: -24:01:00
my two cents about logic:
syntax is "old date" - :"new date", so:
SELECT TIMESTAMPDIFF(SECOND, '2018-11-15 15:00:00', '2018-11-15 15:00:30')
gives 30,
SELECT TIMESTAMPDIFF(SECOND, '2018-11-15 15:00:55', '2018-11-15 15:00:15')
gives: -40
Source: Stackoverflow.com