Format result with concatenated field

Asked

Viewed 87 times

1

I need to bring from my database a TIMEDIFF with field formatted like this: 12:50 (hours and minutes). I use the following excerpt:

CONCAT(HOUR(TIMEDIFF(Campo.expired,NOW())),":",MINUTE(TIMEDIFF(Campo.expired,NOW()))) AS expired

My problem is that when the time is only 1 digit, the result is like this: 2:4 (2 hours and 40 minutes). I need to display this date correctly, that is, 02:40.

I have tried using DATE_FORMAT inside CONCAT, unsuccessfully.

mysql database

  • The column expired is what kind? How did you try to use the DATE_FORMAT?

1 answer

1


You don’t need to use CONCAT to do this.

It can be done like this:

TIME_FORMAT(SEC_TO_TIME(TIMESTAMPDIFF(SECOND, Campo.expired, NOW())), '%H:%i')

This will calculate the difference between dates in seconds, turn the seconds into TIME and use TIME_FORMAT to format the way you want (e.g. 02:40)

Browser other questions tagged mysql database

You are not signed in. Login or sign up in order to post.