Search code examples
mysqlsqldatetimesumdate-arithmetic

How to sum time format (hh:mm:ss.ms) in mysql

I'm trying add time in mysql which is having format (hh:mm:ss.ms) but after addition millisecond value is getting 00

what i tried is

SEC_TO_TIME(sum(time_to_sec(TIMEDURATION)))

as i got to know it is converting time into seconds not getting anything for milisecond. my time format is something like this 19:30:10.903

Solution

  • By design, time_to_sec() loses the fractional seconds. On the other hand, sec_to_time() is capable of properly handling fractional seconds.

    One solution is to sum the fractional seconds separately, like, then add them back before converting back to a time:

    sec_to_time(sum(time_to_sec(timeduration)) + sum(microseconds(timeduration)) / 1000000)