how to get the time average on sqlite

i have a table in SQLite with 3 columns time, scramble and datetime.

i need to calculate the average of the column time it has a format acording to the documentacion of SQLite about date and time data which is 00:00:00.000 %H%M%f

if i use the min or max function

select min(time) from records. or select max(time) from records.

returns a correct values.

but if i use

select avg(time) from records.

it returns 0.0
i have read about it , the documentation of AVG function says dosnt have TEXT as valid data format, i alrready do it programatically but i was wondering if exists a function or query for this.

TFYT.

Solution

First you must use strftime('%s', time) to get the time as unix epoch (seconds since 1970-01-01) and then multiply by 1000 and add the miliseconds.
The result is an integer and can be used to get the average.
This average is a float which must be divided by 1000 and cast to an integer so that time() function can be used to convert it back to TIME:

SELECT time(
          CAST(AVG(strftime('%s', time) * 1000 + SUBSTR(time, -3)) / 1000 AS INTEGER), 
         'unixepoch'
       )
FROM records

Actually you don't get more accuracy by using the milliseconds in the calculation, so get the average separately for the milliseconds and concatenate:

SELECT time(AVG(strftime('%s', time)), 'unixepoch') || '.' ||
       printf('%03d', AVG(SUBSTR(time, -3))) average_time
FROM records

For better accuracy try this:

WITH cte AS (
  SELECT AVG(strftime('%s', time) * 1000 + SUBSTR(time, -3)) avg_seconds
  FROM records
)
SELECT time(CAST(avg_seconds / 1000 AS INTEGER), 'unixepoch') 
       || '.' ||
       printf('%03d', ROUND(avg_seconds) % 1000) average_time
FROM cte

See a simplified demo.