MySQL convert timediff output to day, hour, minute, second format

Question

This is my query:

SELECT TIMEDIFF(end_time,start_time) AS "total" FROM `metrics`;

which gives me:

116:12:10

meaning 116 hours, 12 minutes and 10 seconds.

Instead, I want it to say 4 days 20 hours, 12 minutes etc

Answer 1

SELECT CONCAT(
FLOOR(HOUR(TIMEDIFF('2010-01-06 08:46', '2010-01-01 12:30')) / 24), ' days ',
MOD(HOUR(TIMEDIFF('2010-01-06 08:46', '2010-01-01 12:30')), 24), ' hours ',
MINUTE(TIMEDIFF('2010-01-06 08:46', '2010-01-01 12:30')), ' minutes')

Use your end_time and start_time for the fixed datetime values in my example

As per the two comments below, this solution only works for date differences within 35 days. If you know there are more than 35 days between start and end, i.e. differences over a month, don't use it. Other answers here using TIMESTAMPDIFF will work.

Answer 2

SELECT 
CONCAT(
TIMESTAMPDIFF(day,'2001-01-01 00:00:00','2001-01-02 23:10:00') , ' dagen ',
MOD( TIMESTAMPDIFF(hour,'2001-01-01 00:00:00','2001-01-02 23:10:00'), 24), ' uren ',
MOD( TIMESTAMPDIFF(minute,'2001-01-01 00:00:00','2001-01-02 23:10:00'), 60), ' minuten '
)

Answer 3

I used the answer given by Bodgans, but I added a condition to insert a 0 before quantities less than 10.

CONCAT(
  IF(FLOOR(TIMESTAMPDIFF(SECOND, startDate, endDate) / 86400) < 10, '0', ''),    FLOOR(TIMESTAMPDIFF(SECOND, startDate, endDate) / 86400), ' days ',
  IF(FLOOR((TIMESTAMPDIFF(SECOND, startDate, endDate) % 86400)/3600) < 10, '0', ''), FLOOR((TIMESTAMPDIFF(SECOND, startDate, endDate) % 86400)/3600), ' hours ',
  IF(FLOOR((TIMESTAMPDIFF(SECOND, startDate, endDate) % 3600)/60) < 10, '0', ''), FLOOR((TIMESTAMPDIFF(SECOND, startDate, endDate) % 3600)/60), ' minutes ',
  IF((TIMESTAMPDIFF(SECOND, startDate, endDate) % 60) < 10, '0', ''), (TIMESTAMPDIFF(SECOND, startDate, endDate) % 60),  ' seconds'
)

In that way, we can get a result as follows

09 days 10 hours 06 minutes 30 seconds

Answer 4

Try

SELECT @s:='2016-03-01' started, @e:= '2018-04-21' ended, 
FLOOR((@ms:=TIMESTAMPDIFF(MONTH,@s,@e))/12) yrs, (@ms%12) mns, 
@d:=TIMESTAMPDIFF(DAY,@s:=DATE_ADD(@s,INTERVAL @ms MONTH), @e) dys,
@hr:=TIMESTAMPDIFF(HOUR,@s:=DATE_ADD(@s, INTERVAL @d DAY),@e) hrs,
TIMESTAMPDIFF(MINUTE,TIMESTAMPADD(HOUR,@hr,@s),@e) mins;

This will give you a breakdown from year, month, day, hour and minutes between two dates.

Hope it assists someone.

Answer 5

The easiest way to do this is

CONCAT(
  FLOOR(TIMESTAMPDIFF(SECOND, startDate, endDate) / 86400), ' days ',
  FLOOR((TIMESTAMPDIFF(SECOND, startDate, endDate) % 86400)/3600), ' hours ',
  FLOOR((TIMESTAMPDIFF(SECOND, startDate, endDate) % 3600)/60), ' minutes ',
  (TIMESTAMPDIFF(SECOND, startDate, endDate) % 60),  ' seconds'
)

To show only relevant information you'd need to do a more complex version

IF(
  FLOOR(TIMESTAMPDIFF(SECOND, startDate, endDate) / 86400) = 0,
  IF(
    FLOOR((TIMESTAMPDIFF(SECOND, startDate, endDate) % 86400)/3600) = 0,
    IF(
      FLOOR((TIMESTAMPDIFF(SECOND, startDate, endDate) % 3600)/60) = 0,
      CONCAT((TIMESTAMPDIFF(SECOND, startDate, endDate) % 60),  ' seconds'),
      CONCAT(
        FLOOR((TIMESTAMPDIFF(SECOND, startDate, endDate) % 3600)/60), ' minutes ',
        (TIMESTAMPDIFF(SECOND, startDate, endDate) % 60),  ' seconds'
      )
    ),
    CONCAT(
      FLOOR((TIMESTAMPDIFF(SECOND, startDate, endDate) % 86400)/3600), ' hours ',
      FLOOR((TIMESTAMPDIFF(SECOND, startDate, endDate) % 3600)/60), ' minutes ',
      (TIMESTAMPDIFF(SECOND, startDate, endDate) % 60),  ' seconds'
    )
  ),
  CONCAT(
    FLOOR(TIMESTAMPDIFF(SECOND, startDate, endDate) / 86400), ' days ',
    FLOOR((TIMESTAMPDIFF(SECOND, startDate, endDate) % 86400)/3600), ' hours ',
    FLOOR((TIMESTAMPDIFF(SECOND, startDate, endDate) % 3600)/60), ' minutes ',
    (TIMESTAMPDIFF(SECOND, startDate, endDate) % 60),  ' seconds'
  )
)