CODEWITHSUNDEEP
c#sql
Jonathan Kittell
Jonathan Kittell

Reputation: 7503

Insert Elapsed Time in SQL Server

I want to insert elapsed times using the C# Stopwatch into a SQL Server so we can average the times using a SQL Script.

var stopwatch = new Stopwatch();
stopwatch.Start();
stopwatch.Stop();
var elapsedTime = stopwatch.Elapsed;

I'm inserting the time like this...

cmd.Parameters.Add("@ElapsedTime", SqlDbType.Time);

The data type in the database is ...

ElapsedTime(time(7),null)

Getting this error...

SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

I just want to insert the elapsed time in the database like this...

00:01:44.9383728

Here is how I am putting into database...

internal bool TimesToSql(TimeSpan elapsedTime)
    {
        try
        {
            const string statement = "INSERT INTO Database.dbo.TestProgress(MachineName, ElapsedTime) " +
                                     "VALUES(@MachineName, @ElapsedTime)";
            var conn = new SqlConnection(ConnectionStringLocalDb());
            var cmd = new SqlCommand(statement, conn);
            var machineName = Environment.MachineName;
            cmd.Parameters.Add("@MachineName", SqlDbType.NVarChar);
            cmd.Parameters.Add("@ElapsedTime", SqlDbType.Time);
            //
            cmd.Parameters["@MachineName"].Value = Environment.MachineName;
            cmd.Parameters["@ElapsedTime"].Value = elapsedTime;
            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();

            return true;

        }
        catch (Exception exception)
        {
            _logger.Log(Loglevel.Error, "Boom: {0}", exception.Message);
            return false;
        }
    }

The ultimate goal is to get the average time from let's say x amount of elapsed times, if I can do that using string, then I will format them to strings

Upvotes: 1

Views: 2551

Answers (4)

Marnix van Valen
Marnix van Valen

Reputation: 13673

What exactly do you want to do with the timespan in the database? If all you want to do is sort and aggregate, you could go for a bigint in the database.

Internally in .NET a TimeSpan is nothing but a long value that stores the number of 'Ticks' (see the .NET source code for TimeSpan) and a bunch of conversions of course to seconds, milliseconds etc.

When you load the value again from the database you can recreate the TimeSpan from the ticks stored in the database. You will not lose precision.

You can even perform aggregate functions (Min, Max, Avg, Sum) on the database values and you will still get valid TimeSpans when you later load the value in C#.

If however you have a requirement to query the database for values of, for example, more than 5 mins, you'd need to do the math of figuring out how to express 5 mins as ticks. In the source code linked earlier, you'll see that there are 10,000,000 ticks in a second. So it's hardly rocket science ;)

Upvotes: 1

kevin
kevin

Reputation: 2213

Why not just store the time using a float data type in the database? If you're usually measuring in the order of seconds and minutes then just store the total number of seconds. If you usually measure milliseconds then store the total milliseconds. "Keep It Simple, but don't get stupid."

Unless your application needs to store the time as "X month X days X hours X minutes X.XX seconds" ... which I bet is unlikely the case.

Upvotes: 1

Tak
Tak

Reputation: 1562

Did a quick test in SSMS. I think it's your calculation that is incorrect. Cannot have 84 seconds as maximun is 59.

This did not work because seconds cannot be more than 84.

declare @tim time(7)
set @tim = '00:01:84.9383728'
select @tim

This worked ..

declare @tim time(7)
set @tim = '00:01:59.9383728'
select @tim

Upvotes: 1

Brandon
Brandon

Reputation: 702

If you are certain your elapsedTime is going to be less than 24 hours, you could change

cmd.Parameters["@ElapsedTime"].Value = elapsedTime;

to

cmd.Parameters["@ElapsedTime"].Value = elapsedTime.ToString("hh\:mm\:ss\.ffff");

I'm not sure there's another way to convert a timespan to sql time. Note that if your duration is over 24 hours, the overflow will be truncated.

Upvotes: 1

Related Questions