Time and Dates

[ReggieTheDog]

After much fiddling and bashing I finally managed to get this

SELECT TIME_FORMAT(SEC_TO_TIME(AVG(unix_timestamp(col1)-unix_timestamp(col2))),'%Hh:%im') AS avg

Took me all night to work it out and to craft that little beauty but it works great and spits out figures like this:
02h:13m brilliant… but after let’s say an average duration of 72 hours the numbers start to get a little unweildy. So, I’d like to be able to get this type format - 3d: 04h: 17m
I’ve already tried DATE_FORMAT with unix_timestamp and timestamp but that gives huge day figures… Still scratching my head and wondering if its possible…
Anyone out there that can point me in the right direction?

[r937]

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]

[ReggieTheDog]

@r937 Well I really don’t know how you do it but bravo once again… All makes perfect sense what you’ve done and surprisingly I actually understand it…lol
With a spoonful of experience my biggest challenge was jeust getting my head around it…
Thanks again r937 for coming to the rescue once more… hoorah… long live Coronation Street