Can I add this format in sqlite3

Solution 1:

You can turn your times to seconds using time_to_sec(), sum them, and then turn the result to a time using sec_to_time().

As an example, the following query would compute the total time_spent over the whole table:

select sec_to_time(sum(time_to_sec(time_spent))) total_time_spent
from mytable

This will work even it your times are stored as string, since their format complies to MySQL format for the time datatype.

Note that MySQL time datatype can handle values between '-838:59:59' to '838:59:59' only. If your total time is greater than that, then you will not be able to convert it back to a time.

Solution 2:

Once you store dates in DATE, TIME and DATETIME formats there are a multitude of available date and time functions you can use.

Solution 3:

You can do it with the function time():

select time('00:23:11', '+00:10:20')

or just:

select time('00:23:11', '00:10:20')

Result:

00:33:31

If the sum may exceed 24 hours, for example when you want to add '23:59:59' to '00:23:11' then use this statement:

select 
  case 
    when strftime('%d', datetime('00:23:11', '23:59:59')) = '01' then time('00:23:11', '23:59:59')
    else (24 + time('00:23:11', '23:59:59')) || strftime(':%M:%S', time('00:23:11', '23:59:59')) 
  end

Or:

select 
 (24 * (strftime('%d', datetime('00:23:11', '23:59:59')) - 1) + time('00:23:11', '23:59:59')) || 
 strftime(':%M:%S', time('00:23:11', '23:59:59'))

Result:

24:23:10