BlueSun3k1 BlueSun3k1 - 1 year ago 87
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.

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.


My goal is for the output to display as follows.


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 Source

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...


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')

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download