Is time span a correct phrase

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 . There is no mapping needed. It also works well with SQL code.

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

datetimeoffset

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

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 or 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 from the date to get back the timespan in your business logic.

bigint

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

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 function. Dependent on the required precision, you will need between 8 and 25 characters.

Bonus: Period and Duration

Using a string, you can also store NodaTime datatypes, especially and . 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:

And then use it like

I really like 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 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.

answered Aug 14 '19 at 13:27

MovGP0MovGP0

4,11622 gold badges3030 silver badges3131 bronze badges