BlueSun3k1 BlueSun3k1 - 1 month ago 5
Ajax Question

PHP-MySQL: add leading zero to TIMESTAMPDIFF Concatenated output

I'd like to display a time difference between two datetime and my current approach works, except that when the hours, minutes and seconds are under 10, it only displays a single digit and i'd like to add the leading zero to the output.

the query looks like this.

SELECT *,
CONCAT(
MOD( TIMESTAMPDIFF(hour, start_time, end_time), 24), ':',
MOD( TIMESTAMPDIFF(minute, start_time, end_time), 60), ':',
MOD( TIMESTAMPDIFF(second, start_time, end_time), 60)
) AS total_time
FROM test_db


it outputs the following results when the time digit is less than 10.

4:53:21
10:1:9
6:3:8


My goal is for the output to display as follows.

04:53:21
10:01:09
06:03:08


Note: I am passing this results to an array which is then sent to an AJAX callback to output the results on a dynamically generated table. If there is a better solution that could work using jquery/ajax, then I am more than open to any suggestions. However, since I will eventually need to export this results to an excel file, I have been leaning towards a query solution instead.

Answer

One approach would be to prepend a leading zero, and then take the rightmost two characters...

 RIGHT(CONCAT('0', expr ),2)  

This only works if the values of expr are non-negative, and no more than two digits.


As an entirely different approach, you could get the difference in seconds, and convert to TIME datatype...

  SEC_TO_TIME(MOD(TIMESTAMPDIFF(SECOND,start_time,end_time),24*60*60))

The hour portion could still be less than two digits, so you could apply the same pattern as above... prepending a '0' character, and taking the rightmost eight characters.

Or, you could just wrap that in a DATE_FORMAT( timeexpr, '%T')

Comments