Format result with concatenated field

Question

Format result with concatenated field

Navigation

#1 by (1 votes)

1

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

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

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

I've tried to use DATE_FORMAT within CONCAT without success.

mysql

asked by anonymous 27.01.2015 / 15:26

1 answer

Change Text attribute of a linkbutton within a table in a repeater How to filter a Text in a div with angle JS

score 1 · Accepted Answer

You do not 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 the dates in seconds, transform the seconds into TIME and use TIME_FORMAT to format the way you want (eg 02:40)