TimeSpans, Ticks and BigInts for C#, SQL Server & NHibernate

I'm writing this post about the data type for C# called TimeSpan. The gist of this article is that it's easy to create a TimeSpan in C# and get information about it, but it's tougher to do the same thing for storing its "ticks" units using SQL in a SQL Server database field.

A TimeSpan is not a complicated concept, you may think. It stores the number of units of time up to a point in a certain time range, usually within one day. The units are called "ticks". Here's my example, creating a TimeSpan for the time on a day of 2:30pm.

TimeSpan tSpan = new TimeSpan(14, 30, 00);
Console.WriteLine("Ticks in the TimeSpan: " + tSpan.Ticks);

The output is: 522000000000, i.e. that many ticks have occurred since the start of the time range (midnight, or 00:00:00).

Now, some of  you may have the "privilege" of using the SQL ORM for C#, NHibernate. You'll notice that what NHibernate does for storing TimeSpans in a SQL Server database is convert them into BIGINT numbers. The reason for this, I'm guessing, is that the number of ticks can end up very large for representing a TimeSpan.

If you do a SELECT query on a database table which you've mapped through NHibernate, you would see that number of ticks stored in there when you save your save your entity and execute your session transaction (i.e. ticks is 522,000,000,000).

Now, the key to converting a C# TimeSpan to a BIGINT with SQL is this next number. Once I knew it, it was plain sailing:

The number of ticks in one day = 864,000,000,000

To help you deal with that total-ticks-in-one-day figure, you also need to calculate the number of seconds in one day:

The number of seconds in one day = 86,400
(ie. 60 seconds x 60 minutes x 24 hours)

Now, when you calculate the total ticks in one day divided by the total seconds in one day, you get this number:

The number of ticks per second = 10,000,000
(i.e. 864000000000 ticks / 86400 seconds)

So, from there, stop and think. If you can get your TimeSpan's hours, minutes and seconds, all you will need to do to turn this into a total number of ticks is:

• convert each figure's value into a total number of seconds
• multiple the sum of all these seconds values by 10,000,000

e.g. for my 2:30pm TimeSpan:

((14 * 60 * 60) + (30 * 60) + 0 * 10000000
= 522000000000

That figure is that same as the one NHibernate stored. We're on the right track! So, let's turn this into a Transact-SQL statement, for inserting a record with those calculations in it. I'm going to declare my hour, minutes and seconds values as variables beforehand.

GO
declare @myHours int = 14;
declare @myMinutes int = 30;
declare @mySeconds int = 0;
declare @ticksPerSecond bigint = 10000000;
INSERT INTO [dbo].[MyTable]
     (
     [TimeOfDay]
     )
     VALUES
     (
     ((@myHours * 60 * 60) + (@myMinutes * 60) + (@mySeconds)) * @ticksPerSecond
     )
GO

My desired result (of 522,000,000,000) for the TimeOfDay field appears in the database correctly. T-SQL stores the value as a BIGINT in the same way that NHibernate stores a C# TimeSpan.