thanks for the challenge, this was fun –
[code]CREATE TABLE 2datetimes
( datetime1 datetime
, datetime2 datetime
);
INSERT INTO 2datetimes (datetime1,datetime2) VALUES
( ‘2015-01-01 09:37’ , ‘2015-02-01 14:44’)
,( ‘2014-01-01 09:37’ , ‘2015-01-01 14:44’)
,( ‘2015-08-01 09:37’ , ‘2015-08-01 14:44’)
,( ‘2015-08-01 09:37’ , ‘2015-08-02 14:44’)
,( ‘2015-08-01 09:37’ , ‘2015-08-01 10:36’)
,( ‘2015-08-01 09:37’ , ‘2015-08-01 10:44’)
,( ‘2015-08-01 09:37:00’ , ‘2015-08-01 9:37:01’)
,( ‘2015-08-01 09:37:03’ , ‘2015-08-01 9:38:02’)
;
SELECT *
, CONCAT(
CASE WHEN days = 0
THEN ‘’
WHEN days = 1
THEN ‘1 day ’
ELSE CONCAT(’‘,days,’ days ‘)
END
, CASE WHEN hrs = 0
THEN ‘’
WHEN hrs = 1
THEN ‘1 hr ’
ELSE CONCAT(’’,hrs,’ hrs ‘)
END
, CASE WHEN ROUND(mins) = 1
THEN ‘1 min’
ELSE CONCAT(’‘,ROUND(mins),’ mins’)
END
) AS result
FROM (
SELECT *
, diff1/3600 AS diff2
, floor(diff1/3600) AS hrs
, ( diff1/3600 - floor(diff1/3600) )60 AS mins
FROM (
SELECT *
, days86400 AS daysecs
, secs - days*86400 AS diff1
FROM (
SELECT datetime1
, datetime2
, datediff(datetime2,datetime1) AS days
, unix_timestamp(datetime2)-unix_timestamp(datetime1) AS secs
FROM 2datetimes
) AS q1
) AS q2
) AS q3[/code]