Field format for saving time

Asked

Viewed 605 times

4

Which field should I use in SQL Server to save a duration? Ex.: 2days 23H 47min 00Sec.

If you have an example with Fluent API it will help a lot.

c# asp.net-mvc .net sql-server entity-framework

2 answers

5


The correct type of . NET to represent time lengths is the TimeSpan.

It is possible to use type Time to store this data, according to this compatibility table.

Using . NET 6 forward it is possible to adopt the TimeOnly.

Something that people usually do is also use a INT or BIGINT to guard the ticks or millisecond or even seconds, according to the precision you need.

Since the question gives no details, I cannot go beyond this.

  • When I use Timespan the EF is mapping to Time(7). When trying to insert I get this error "Sqldbtype. The value '3.03:03:00' is out of range. It should be between 00:00:00.0000000 and 23:59:59.9999999."

  • This is already another problem, put in detail in a new question. Without details of what you are doing we have no way to help.

0

To store a time count that is less than 24 hours, you can use TIME. But if the accumulated exceeds 24 hours, as you quote, then you can use either datetime or int.

When using the datetime type, you should start with 0. After, add/subtract the hours, minutes and seconds. At any time, to display the value in the format hhh:mm, you can use the formula 

   (valor / 60) + ':' + (valor % 60)

To assess what happens when using datetime:

-- código #1
declare @Horas datetime;

-- zera a variável
set @Horas= 0;
PRINT @Horas

-- soma 40 horas
set @Horas= DateAdd(hour, +40, @Horas);
PRINT @Horas

-- soma 38 minutos
set @Horas= DateAdd(minute, +38, @Horas);
PRINT @Horas

-- subtrai 128 minutos
set @Horas= DateAdd(minute, -128, @Horas);
PRINT @Horas

-- exibe no formato hhh:mm
PRINT Cast(DateDiff(minute, 0, @Horas) / 60 as varchar(10)) + 'h' + Right('00' + Cast(DateDiff(minute, 0, @Horas) % 60 as varchar(2)), 2) + 'min'

But a column of type int occupies less space than one of type datetime. Therefore, it seems to me that the best implementation remains with an INT column.

Browser other questions tagged c# asp.net-mvc .net sql-server entity-framework

You are not signed in. Login or sign up in order to post.