Storing Lap Times SQL Server

Question

Whats the best solution to store lap times on a SQL-Server database? the suggested format should be 01:52.525. I'm using time(3) but it does not allow me to use the Avg() Function.

Best regards,

Answer 1

Try storing the start time and duration (lap time) in epoch milliseconds, then use a SQL query (which would allow Avg()):

SELECT (duration - start) FROM lap_times (WHERE ...)

Refer to this answer for converting milliseconds to a human-readable timestamp.

Answer 2

Store the times as time. Add computed columns with the values as milliseconds. Use the computed columns for calculations.

For example:

alter table t add laptime_ms as (datediff(ms, 0, laptime));

You can now use this for averages, and convert back to time as:

select convert(time, dateadd(ms, 0, avg(laptime_ms)))
from t

Answer 3

Since you are already storing the lap duration as time, you can use DATEDIFF to calculate duration in milliseconds for the average calc and use DATEADD to convert milliseconds to time.

SELECT 
      AVG(DATEDIFF(millisecond, '', LapTime)) AS AvgLapTimeMilliseconds
    , CAST(DATEADD(millisecond, AVG(DATEDIFF(millisecond, '', LapTime)), '') AS time(3)) AS AvgLapTime
FROM dbo.Lap;