1

I have checked this question (PHP/MySQL Display time since user's last login) and it didn't helped me.

I have a user table in MySQL database. Here are the fields of user table.

uid
name
password
last_login (type = timestamp)

Whenever user login to website last_login field get updated with current date time (yyyy-mm-dd hh:mm:ss).

I am able to update last_login field and it is working properly.

Now, I want to show difference between now and last_login time. I tried SQL query to get the detail but it is not working. Here is the SQL query.

mysql_query("SELECT DATEDIFF(NOW(), last_login) FROM user where uid=1");

I tried to check this query in phpMyAdmin but it returns 0.

Please advise what I am doing wrong.

Community
  • 1
  • 1
Roxx
  • 3,738
  • 20
  • 92
  • 155
  • `0` means the user has logged in in the past day. Update user 1's last_login time to `2015-05-25 00:00:00`. You should get back `5`, I think. – chris85 May 30 '15 at 17:02
  • how can i show it in year/month/day/hour/min/sec. – Roxx May 30 '15 at 17:03
  • 1
    have you tried timediff instead of datediff? – Pavlin May 30 '15 at 17:03
  • ^ timediff should do it. http://stackoverflow.com/questions/11579946/only-show-hours-in-mysql-datediff – chris85 May 30 '15 at 17:05
  • @pavlin chris85 Does timediff can give the output if hour is greater 24 then show 1 day. Similar for month year. – Roxx May 30 '15 at 17:08
  • 1
    Actually check out this thread. http://stackoverflow.com/questions/28111956/how-to-get-data-from-last-hour-last-day-and-last-month-with-one-query I think that is what you are after.. – chris85 May 30 '15 at 17:11
  • @chris85 It didn't helped. I think i will have to look for other php solution. – Roxx May 30 '15 at 17:29
  • Just to check for the obvious here. If you run the difference query right *after* updating the last login field, the result of zero would be kinda obvious, if you check for datediff instead of timediff... – Markus AO May 30 '15 at 18:22

2 Answers2

1
"SELECT DATEDIFF(NOW(), last_login) FROM user where uid=1"

Returns 0, as the user has logged in today (days since last login)

You may use TIMESTAMPDIFF as well

"SELECT TIMEDIFF(NOW(), last_login) FROM user where uid=1"

For reference:

https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_timediff

mysqlrockstar
  • 2,536
  • 1
  • 19
  • 36
  • Thanks for your answer but if user last logged on 2 days ago then it show it in hour. I don't want this. I want if its less than a day then show it in hour if its less than 30 days then show it as number of days. same for month and year. – Roxx May 30 '15 at 17:12
  • In MySQL we can do that using `CASE WHEN`, but its better to do that in PHP considering performance. Check [this link](http://stackoverflow.com/questions/2915864/php-how-to-find-the-time-elapsed-since-a-date-time) – mysqlrockstar May 30 '15 at 17:28
1

Here's a modified version of this answer, How to get data from last hour, last day and last month with one query?. 

    SELECT CASE WHEN DATE_SUB(NOW() , INTERVAL 1 HOUR ) <= last_login THEN concat(TIMESTAMPDIFF(Minute, last_login, NOW()), ' Minute(s)') ELSE 0 END HOURLY, 
CASE WHEN DATE_SUB( NOW( ) , INTERVAL 1 DAY ) <= last_login THEN concat(TIMESTAMPDIFF(Hour, last_login, NOW()), ' Hour(s)') ELSE 0 
END DAILY, 
CASE WHEN DATE_SUB( NOW( ) , INTERVAL 1 MONTH ) <= last_login THEN concat(TIMESTAMPDIFF(DAY, last_login, NOW()), ' Day(s)') ELSE 0 
END MONTHLY,
CASE WHEN DATE_SUB( NOW( ) , INTERVAL 1 YEAR ) <= last_login THEN concat(TIMESTAMPDIFF(MONTH, last_login, NOW()), ' Month(s)') ELSE 0 
END YEAR
FROM users WHERE userid = 2203 or userid = 1 or userid = 1746

The first column to have a value 1 is when the user last logged in. The later columns will still validate because if they've logged in in the past hour they've logged in in the past day, etc.

I'm not sure if this is easier to read now or not, it's no longer an empty/not check. Now it will display the difference in time from their last login in the column; the other greater columns (month, year, etc) will say 0s with the unit..

Community
  • 1
  • 1
chris85
  • 23,846
  • 7
  • 34
  • 51