how to get the time interval of a date and a datetime displayed as day,hour, min,seconds in sql

Question

I want to get the difference of a date and a datetime and display it as elapse time, example: 4days 7hr 8min 3sec.. Have tried this but only gives me the difference of the date part:

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

You can use the TIMESTAMPDIFF() function along with CONCAT() to get the formatting you want:

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

Output:

4days 7hr 8min 3sec