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