What is the correct SQL type to store a .Net Timespan with values > 24:00:00?

Solution 1:

I'd store it in the database as a BIGINT and I'd store the number of ticks (eg. TimeSpan.Ticks property).

That way, if I wanted to get a TimeSpan object when I retrieve it, I could just do TimeSpan.FromTicks(value) which would be easy.

Solution 2:

Thanks for the advice. As there is no equivalent in SQL server. I simply created a 2nd field which converted the TimeSpan to ticks and stored that in the DB. I then prevented storing the TimeSpan

public Int64 ValidityPeriodTicks { get; set; }

[NotMapped]
public TimeSpan ValidityPeriod
{
    get { return TimeSpan.FromTicks(ValidityPeriodTicks); }
    set { ValidityPeriodTicks = value.Ticks; }
}

Solution 3:

If you don't have to store more than 24 hours you can just store time, since SQL Server 2008 and later the mapping is

time (SQL Server) <-> TimeSpan(.NET)

No conversions needed if you only need to store 24 hours or less.

Source: http://msdn.microsoft.com/en-us/library/cc716729(v=vs.110).aspx

But, if you want to store more than 24h, you are going to need to store it in ticks, retrieve the data and then convert to TimeSpan. For example

int timeData = yourContext.yourTable.FirstOrDefault();
TimeSpan ts = TimeSpan.FromMilliseconds(timeData);

Solution 4:

There isn't a direct equivalent. Just store it numerically, e.g. number of seconds or something appropriate to your required accuracy.

Solution 5:

There are multiple ways how to present a timespan in the database.

time

This datatype is supported since SQL Server 2008 and is the prefered way to store a TimeSpan. There is no mapping needed. It also works well with SQL code.

public TimeSpan ValidityPeriod { get; set; }

However, as stated in the original question, this datatype is limited to 24 hours.

datetimeoffset

The datetimeoffset datatype maps directly to System.DateTimeOffset. It's used to express the offset between a datetime/datetime2 to UTC, but you can also use it for TimeSpan.

However, since the datatype suggests a very specific semantic, so you should also consider other options.

datetime / datetime2

One approach might be to use the datetime or datetime2 types. This is best in scenarios where you need to process the values in the database directly, ie. for views, stored procedures, or reports. The drawback is that you need to substract the value DateTime(1900,01,01,00,00,00) from the date to get back the timespan in your business logic.

public DateTime ValidityPeriod { get; set; }

[NotMapped]
public TimeSpan ValidityPeriodTimeSpan
{
    get { return ValidityPeriod - DateTime(1900,01,01,00,00,00); }
    set { ValidityPeriod = DateTime(1900,01,01,00,00,00) + value; }
}

bigint

Another approach might be to convert the TimeSpan into ticks and use the bigint datatype. However, this approach has the drawback that it's cumbersome to use in SQL queries.

public long ValidityPeriod { get; set; }

[NotMapped]
public TimeSpan ValidityPeriodTimeSpan
{
    get { return TimeSpan.FromTicks(ValidityPeriod); }
    set { ValidityPeriod = value.Ticks; }
}

varchar(N)

This is best for cases where the value should be readable by humans. You might also use this format in SQL queries by utilizing the CONVERT(datetime, ValidityPeriod) function. Dependent on the required precision, you will need between 8 and 25 characters.

public string ValidityPeriod { get; set; }

[NotMapped]
public TimeSpan ValidityPeriodTimeSpan
{
    get { return TimeSpan.Parse(ValidityPeriod); }
    set { ValidityPeriod = value.ToString("HH:mm:ss"); }
}

Bonus: Period and Duration

Using a string, you can also store NodaTime datatypes, especially Duration and Period. The first is basically the same as a TimeSpan, while the later respects that some days and months are longer or shorter than others (ie. January has 31 days and February has 28 or 29; some days are longer or shorter because of daylight saving time). In such cases, using a TimeSpan is the wrong choice.

You can use this code to convert Periods:

using NodaTime;
using NodaTime.Serialization.JsonNet;

internal static class PeriodExtensions
{
    public static Period ToPeriod(this string input)
    {
        var js = JsonSerializer.Create(new JsonSerializerSettings());
        js.ConfigureForNodaTime(DateTimeZoneProviders.Tzdb);
        var quoted = string.Concat(@"""", input, @"""");
        return js.Deserialize<Period>(new JsonTextReader(new StringReader(quoted)));
    }
}

And then use it like

public string ValidityPeriod { get; set; }

[NotMapped]
public Period ValidityPeriodPeriod
{
    get => ValidityPeriod.ToPeriod();
    set => ValidityPeriod = value.ToString();
}

I really like NodaTime and it often saves me from tricky bugs and lots of headache. The drawback here is that you really can't use it in SQL queries and need to do calculations in-memory.

CLR User-Defined Type

You also have the option to use a custom datatype and support a custom TimeSpan class directly. See CLR User-Defined Types for details.

The drawback here is that the datatype might not behave well with SQL Reports. Also, some versions of SQL Server (Azure, Linux, Data Warehouse) are not supported.

Value Conversions

Starting with EntityFramework Core 2.1, you have the option to use Value Conversions.

However, when using this, EF will not be able to convert many queries into SQL, causing queries to run in-memory; potentially transfering lots and lots of data to your application.

So at least for now, it might be better not to use it, and just map the query result with Automapper.