mysql - 如何在 SQL 中将选定的日期列格式化为相对时间格式

Question

如果我们有一个带有时间戳字段的表，我想将日期显示为时间之前(类似于我们在 Java/PHP 中所做的方式)

MySQL 中是否有现成的函数可以完成这项工作？

输出应该是相对时间而不是通常的日期时间，例如0000-00-00 00:00:00

    USER    |    LAST_LOGGED_IN
_____________________________________
    ABC     |    5 minutes ago
_____________________________________
    PQR     |    10 minutes ago
_____________________________________
    XYZ     |    2 weeks ago
_____________________________________
    XXX     |    1 month ago

Answer 1

使用timestampdiff函数:

select       
t.userid,     
case when t.years > 0 
     then concat( t.years ,' year', case when t.years > 1
                                         then 's' else '' end, ' ago')  
     when t.months > 0         
     then concat( t.months, ' month', case when t.months > 1    
                                           then 's' else '' end, ' ago')
     when t.weeks > 0 
     then concat( t.weeks , ' week', case when t.weeks > 1 
                                          then 's' else '' end, ' ago')  
     when t.days > 0  
     then concat( t.days , ' day', case when t.days > 1 
                                        then 's' else '' end, ' ago')  
     when t.hours > 0 
     then concat( t.hours, ' hour',  case when t.hours > 1 
                                          then 's' else '' end, ' ago')  
     when t.minutes > 0
     then concat( t.minutes, ' minute', case when t.minutes > 1  
                                          then 's' else '' end, ' ago')    
     else concat( t.seconds, ' second', case when t.seconds > 1
                                          then 's' else '' end, ' ago')
end as last_login  
from  
(  
select   
    userid,   
    timestampdiff(second, login_time, now()) as seconds,  
    timestampdiff(minute, login_time, now()) as minutes,  
    timestampdiff(hour, login_time,now()) as hours,  
    timestampdiff(day, login_time,now()) as days,  
    timestampdiff(week, login_time,now()) as weeks,  
    timestampdiff(month, login_time, now()) as months,  
    timestampdiff(year, login_time, now()) as years 
from user_login ) t  

sqlfiddle:http://sqlfiddle.com/#!9/a2cbee/5