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

gr8qqesn  于 2021-06-21  发布在  Mysql
关注(0)|答案(1)|浏览(501)

如果我们有一个带有时间戳字段的表,我想将日期显示为 time ago (类似于我们在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
gopyfrb3

gopyfrb31#

使用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号

相关问题