如何在sql中获得日期和日期时间的时间间隔显示为天，小时，分钟，秒

Question

我想获得日期和日期时间的差异，并将其显示为运行时间，例如:4days 7hr 8min 3sec..我试过这个，但只给我日期部分的差异:

SELECT a.id, b.creation_date ,c.user_name,d.operation,d.system_,e.name,
            e.email_address,e.office,(coalesce(s.id, 0)) as status,t.status as status_name,b.attachment,
            date_format(a.date ,'%Y-%m-%d') as dateTakenStage, 
            date_format(a.date,'%h:%i:%s %p') as timeTakenStage,
            date_format(s.date ,'%Y-%m-%d') as dateTakenStatus,datediff(s.date,b.creation_date) as dated,
            date_format(s.date,'%h:%i:%s %p') as timeTakenStatus,a.stage,s.details 
            FROM job_order_stage a
            INNER JOIN job_order b
            ON a.job_order=b.id
            INNER JOIN job_order_type d
            ON b.job_order_type=d.id
            INNER JOIN client e
            ON b.client=e.id 
            INNER JOIN account f
            ON b.assigned_to=f.id
            INNER JOIN user c
            ON f.user=c.user_id
            LEFT outer JOIN job_order_status s
            ON s.job_order_stage = a.id 
            LEFT JOIN stage_status ss
            ON s.stage_status = ss.id 
            Left JOIN status t
            ON ss.status=t.id
            WHERE b.id='201506106'
            order by a.date desc, s.date desc;

Answer 1

您可以将TIMESTAMPDIFF()函数与CONCAT()一起使用，以获得您想要的格式:

SELECT CONCAT(TIMESTAMPDIFF(DAYS, s.date, b.creation_date), 'days ',
              MOD(TIMESTAMPDIFF(HOUR, s.date, b.creation_date), 24), 'hr ',
              MOD(TIMESTAMPDIFF(MINUTE, s.date, b.creation_date), 60), 'min ',
              MOD(TIMESTAMPDIFF(SECOND, s.date, b.creation_date), 60), 'sec') AS dated

输出:

4days 7hr 8min 3sec