Qual é o tipo correto de SQL para armazenar um Timespan .Net com valores> 24:00:00?

Eu estou tentando armazenar um .net TimeSpan no SQL Server 2008 R2.

A EF Code First parece estar sugerindo que ela deve ser armazenada como um Time(7) no SQL.

No entanto, o TimeSpan in .Net pode suportar períodos mais longos que 24 horas.

Qual é a melhor maneira de lidar com o armazenamento de .net TimeSpan no servidor SQL?

Eu armazenaria no database como um BIGINT e armazenaria o número de ticks (por exemplo, propriedade TimeSpan.Ticks ).

Dessa forma, se eu quisesse obter um object TimeSpan quando eu recuperá-lo, eu poderia apenas fazer TimeSpan.FromTicks (valor), que seria fácil.

Obrigado pelo conselho. Como não há equivalente no servidor SQL. Eu simplesmente criei um segundo campo que converteu o TimeSpan em ticks e o armazenou no database. Eu então evitei armazenar o TimeSpan

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

Se você não precisa armazenar mais de 24 horas, basta armazenar o tempo , já que o SQL Server 2008 e mais tarde o mapeamento é

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

Não são necessárias conversões se você precisar armazenar apenas 24 horas ou menos.

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

Mas , se você quiser armazenar mais de 24h, precisará armazená-lo em ticks, recuperar os dados e depois convertê-los em TimeSpan. Por exemplo

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

Não há um equivalente direto. Basta armazená-lo numericamente, por exemplo, número de segundos ou algo apropriado à sua precisão exigida.

Eu sei que esta é uma pergunta antiga, mas eu queria ter certeza de que algumas outras opções sejam observadas.

Desde que você não pode armazenar um TimeSpan maior que 24 horas em um campo de tipo de dados sql tempo; algumas outras opções podem ser.

  1. Use um varchar (xx) para armazenar o ToString do TimeSpan. O benefício disso é que a precisão não precisa ser inserida no tipo de dados ou no cálculo (segundos versus milésimos de segundo vs dias versus quinzenas). Tudo o que você precisa é usar TimeSpan.Parse / TryParse. Isso é o que eu faria.

  2. Use uma segunda data, datetime ou datetimeoffset, que armazena o resultado da primeira data + timespan. A leitura do database é uma questão de TimeSpan x = SecondDate – FirstDate. O uso dessa opção protegerá você para outras bibliotecas de access a dados não .NET que acessam os mesmos dados, mas não entendem os TimeSpans; no caso de você ter esse ambiente.

Para ser consistente com o que provavelmente é a fonte mais provável de gerar um período de tempo (calculando a diferença de 2 vezes ou de data e hora), talvez você queira armazenar um TimeSpan .NET como um Tipo de Data e Hora do SQL Server.

Isso ocorre porque, no SQL Server, a diferença de 2 DateTime ( Cast para Float e, depois, Cast para um DateTime ) é simplesmente um DateTime relativo a 1 de janeiro de 1900. Ex. Uma diferença de +0,1 segundo seria 1 de janeiro de 1900 00: 00: 00.100 e -0,1 segundo seria 31 de dezembro de 1899 23: 59: 59,900.

Para converter um TimeSpan .NET em um Tipo de DateTime SQL Server, você primeiro o converteria em um tipo de DateTime .NET adicionando-o a um DateTime de 1 de janeiro de 1900. É claro, quando você o lê no .NET do SQL Server , você deve primeiro lê-lo em um DateTime .NET e, em seguida, subtrair 1 de janeiro de 1900 dele para convertê-lo em um TimeSpan .NET.

Para casos de uso em que os períodos de tempo estão sendo gerados a partir do SQL Server DateTime e no SQL Server (ou seja, via T-SQL) e SQL Server antes de 2016, dependendo das necessidades de intervalo e precisão, pode não ser prático armazenar eles como milissegundos (para não mencionar Ticks ) porque o tipo Int retornado por DateDiff (vs. o BigInt do SS 2016 + DateDiff_Big ) estouros após ~ 24 dias no valor de milissegundos e ~ 67 anos. de segundos. Considerando que, esta solução irá lidar com períodos de tempo com precisão até 0,1 segundos e de -147 a +8.099 anos.

ADVERTÊNCIAS:

  1. Isso só funcionaria se a diferença em relação a 1 de janeiro de 1900 resultasse em um valor dentro do intervalo de um tipo de DateTime SQL Server (1º de janeiro de 1753 a 31 de dezembro de 9999, conhecido como -147 a +8.099 anos). Não precisamos nos preocupar tanto com o lado do TimeSpan do .NET, pois ele pode aguentar ~ 29 k até +29 k anos. Não mencionei o tipo DateTime2 do SQL Server (cujo intervalo, no lado negativo, é muito maior que o do DateTime do SQL Server), porque: a) ele não pode ser convertido em um numérico por meio de um simples Cast e b) DateTime ‘ s intervalo deve ser suficiente para a grande maioria dos casos de uso.

  2. Diferenças de DateTime SQL Server computadas por meio do método Cast -to- Float e back não parecem ser precisas além de 0,1 segundos.

Normalmente, eu armazeno um TimeSpan como um bigint preenchido com carrapatos da propriedade TimeSpan.Ticks como sugerido anteriormente. Você também pode armazenar um TimeSpan como um varchar (26) preenchido com a saída de TimeSpan.ToString (). As quatro funções escalares (ConvertFromTimeSpanString, ConvertToTimeSpanString, DateAddTicks, DateDiffTicks) que escrevi são úteis para manipular o TimeSpan no lado do SQL e evitar os hacks que produziriam intervalos limitados artificialmente. Se você puder armazenar o intervalo em um .NET TimeSpan, ele também deverá funcionar com essas funções. Além disso, as funções permitem que você trabalhe com TimeSpans e 100 nanossegundos, mesmo quando estiver usando tecnologias que não incluam o .NET Framework.

 DROP FUNCTION [dbo].[DateDiffTicks] GO DROP FUNCTION [dbo].[DateAddTicks] GO DROP FUNCTION [dbo].[ConvertToTimeSpanString] GO DROP FUNCTION [dbo].[ConvertFromTimeSpanString] GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO -- ============================================= -- Author: James Coe -- Create date: 2011-05-23 -- Description: Converts from a varchar(26) TimeSpan string to a bigint containing the number of 100 nanosecond ticks. -- ============================================= /* [-][d.]hh:mm:ss[.fffffff] "-" A minus sign, which indicates a negative time interval. No sign is included for a positive time span. "d" The number of days in the time interval. This element is omitted if the time interval is less than one day. "hh" The number of hours in the time interval, ranging from 0 to 23. "mm" The number of minutes in the time interval, ranging from 0 to 59. "ss" The number of seconds in the time interval, ranging from 0 to 59. "fffffff" Fractional seconds in the time interval. This element is omitted if the time interval does not include fractional seconds. If present, fractional seconds are always expressed using seven decimal digits. */ CREATE FUNCTION [dbo].[ConvertFromTimeSpanString] (@timeSpan varchar(26)) RETURNS bigint AS BEGIN DECLARE @hourStart int DECLARE @minuteStart int DECLARE @secondStart int DECLARE @ticks bigint DECLARE @hours bigint DECLARE @minutes bigint DECLARE @seconds DECIMAL(9, 7) SET @hourStart = CHARINDEX('.', @timeSpan) + 1 SET @minuteStart = CHARINDEX(':', @timeSpan) + 1 SET @secondStart = CHARINDEX(':', @timespan, @minuteStart) + 1 SET @ticks = 0 IF (@hourStart > 1 AND @hourStart < @minuteStart) BEGIN SET @ticks = CONVERT(bigint, LEFT(@timespan, @hourstart - 2)) * 864000000000 END ELSE BEGIN SET @hourStart = 1 END SET @hours = CONVERT(bigint, SUBSTRING(@timespan, @hourStart, @minuteStart - @hourStart - 1)) SET @minutes = CONVERT(bigint, SUBSTRING(@timespan, @minuteStart, @secondStart - @minuteStart - 1)) SET @seconds = CONVERT(DECIMAL(9, 7), SUBSTRING(@timespan, @secondStart, LEN(@timeSpan) - @secondStart + 1)) IF (@ticks < 0) BEGIN SET @ticks = @ticks - @hours * 36000000000 END ELSE BEGIN SET @ticks = @ticks + @hours * 36000000000 END IF (@ticks < 0) BEGIN SET @ticks = @ticks - @minutes * 600000000 END ELSE BEGIN SET @ticks = @ticks + @minutes * 600000000 END IF (@ticks < 0) BEGIN SET @ticks = @ticks - @seconds * 10000000.0 END ELSE BEGIN SET @ticks = @ticks + @seconds * 10000000.0 END RETURN @ticks END GO -- ============================================= -- Author: James Coe -- Create date: 2011-05-23 -- Description: Converts from a bigint containing the number of 100 nanosecond ticks to a varchar(26) TimeSpan string. -- ============================================= /* [-][d.]hh:mm:ss[.fffffff] "-" A minus sign, which indicates a negative time interval. No sign is included for a positive time span. "d" The number of days in the time interval. This element is omitted if the time interval is less than one day. "hh" The number of hours in the time interval, ranging from 0 to 23. "mm" The number of minutes in the time interval, ranging from 0 to 59. "ss" The number of seconds in the time interval, ranging from 0 to 59. "fffffff" Fractional seconds in the time interval. This element is omitted if the time interval does not include fractional seconds. If present, fractional seconds are always expressed using seven decimal digits. */ CREATE FUNCTION [dbo].[ConvertToTimeSpanString] (@ticks bigint) RETURNS varchar(26) AS BEGIN DECLARE @timeSpanString varchar(26) IF (@ticks < 0) BEGIN SET @timeSpanString = '-' END ELSE BEGIN SET @timeSpanString = '' END -- Days DECLARE @days bigint SET @days = FLOOR(ABS(@ticks / 864000000000.0)) IF (@days > 0) BEGIN SET @timeSpanString = @timeSpanString + CONVERT(varchar(26), @days) + '.' END SET @ticks = ABS(@ticks % 864000000000) -- Hours SET @timeSpanString = @timeSpanString + RIGHT('0' + CONVERT(varchar(26), FLOOR(@ticks / 36000000000.0)), 2) + ':' SET @ticks = @ticks % 36000000000 -- Minutes SET @timeSpanString = @timeSpanString + RIGHT('0' + CONVERT(varchar(26), FLOOR(@ticks / 600000000.0)), 2) + ':' SET @ticks = @ticks % 600000000 -- Seconds SET @timeSpanString = @timeSpanString + RIGHT('0' + CONVERT(varchar(26), FLOOR(@ticks / 10000000.0)), 2) SET @ticks = @ticks % 10000000 -- Fractional Seconds IF (@ticks > 0) BEGIN SET @timeSpanString = @timeSpanString + '.' + LEFT(CONVERT(varchar(26), @ticks) + '0000000', 7) END RETURN @timeSpanString END GO -- ============================================= -- Author: James Coe -- Create date: 2011-05-23 -- Description: Adds the specified number of 100 nanosecond ticks to a date. -- ============================================= CREATE FUNCTION [dbo].[DateAddTicks] ( @ticks bigint , @starting_date datetimeoffset ) RETURNS datetimeoffset AS BEGIN DECLARE @dateTimeResult datetimeoffset IF (@ticks < 0) BEGIN -- Hours SET @dateTimeResult = DATEADD(HOUR, CEILING(@ticks / 36000000000.0), @starting_date) SET @ticks = @ticks % 36000000000 -- Seconds SET @dateTimeResult = DATEADD(SECOND, CEILING(@ticks / 10000000.0), @dateTimeResult) SET @ticks = @ticks % 10000000 -- Nanoseconds SET @dateTimeResult = DATEADD(NANOSECOND, @ticks * 100, @dateTimeResult) END ELSE BEGIN -- Hours SET @dateTimeResult = DATEADD(HOUR, FLOOR(@ticks / 36000000000.0), @starting_date) SET @ticks = @ticks % 36000000000 -- Seconds SET @dateTimeResult = DATEADD(SECOND, FLOOR(@ticks / 10000000.0), @dateTimeResult) SET @ticks = @ticks % 10000000 -- Nanoseconds SET @dateTimeResult = DATEADD(NANOSECOND, @ticks * 100, @dateTimeResult) END RETURN @dateTimeResult END GO -- ============================================= -- Author: James Coe -- Create date: 2011-05-23 -- Description: Gets the difference between two dates in 100 nanosecond ticks. -- ============================================= CREATE FUNCTION [dbo].[DateDiffTicks] ( @starting_date datetimeoffset , @ending_date datetimeoffset ) RETURNS bigint AS BEGIN DECLARE @ticks bigint DECLARE @days bigint DECLARE @hours bigint DECLARE @minutes bigint DECLARE @seconds bigint SET @hours = DATEDIFF(HOUR, @starting_date, @ending_date) SET @starting_date = DATEADD(HOUR, @hours, @starting_date) SET @ticks = @hours * 36000000000 SET @seconds = DATEDIFF(SECOND, @starting_date, @ending_date) SET @starting_date = DATEADD(SECOND, @seconds, @starting_date) SET @ticks = @ticks + @seconds * 10000000 SET @ticks = @ticks + CONVERT(bigint, DATEDIFF(NANOSECOND, @starting_date, @ending_date)) / 100 RETURN @ticks END GO --- BEGIN Test Harness --- SET NOCOUNT ON DECLARE @dateTimeOffsetMinValue datetimeoffset DECLARE @dateTimeOffsetMaxValue datetimeoffset DECLARE @timeSpanMinValueString varchar(26) DECLARE @timeSpanZeroString varchar(26) DECLARE @timeSpanMaxValueString varchar(26) DECLARE @timeSpanMinValueTicks bigint DECLARE @timeSpanZeroTicks bigint DECLARE @timeSpanMaxValueTicks bigint DECLARE @dateTimeOffsetMinMaxDiffTicks bigint DECLARE @dateTimeOffsetMaxMinDiffTicks bigint SET @dateTimeOffsetMinValue = '0001-01-01T00:00:00.0000000+00:00' SET @dateTimeOffsetMaxValue = '9999-12-31T23:59:59.9999999+00:00' SET @timeSpanMinValueString = '-10675199.02:48:05.4775808' SET @timeSpanZeroString = '00:00:00' SET @timeSpanMaxValueString = '10675199.02:48:05.4775807' SET @timeSpanMinValueTicks = -9223372036854775808 SET @timeSpanZeroTicks = 0 SET @timeSpanMaxValueTicks = 9223372036854775807 SET @dateTimeOffsetMinMaxDiffTicks = 3155378975999999999 SET @dateTimeOffsetMaxMinDiffTicks = -3155378975999999999 -- TimeSpan Conversion Tests PRINT 'Testing TimeSpan conversions...' DECLARE @convertToTimeSpanStringMinTicksResult varchar(26) DECLARE @convertFromTimeSpanStringMinTimeSpanResult bigint DECLARE @convertToTimeSpanStringZeroTicksResult varchar(26) DECLARE @convertFromTimeSpanStringZeroTimeSpanResult bigint DECLARE @convertToTimeSpanStringMaxTicksResult varchar(26) DECLARE @convertFromTimeSpanStringMaxTimeSpanResult bigint SET @convertToTimeSpanStringMinTicksResult = dbo.ConvertToTimeSpanString(@timeSpanMinValueTicks) SET @convertFromTimeSpanStringMinTimeSpanResult = dbo.ConvertFromTimeSpanString(@timeSpanMinValueString) SET @convertToTimeSpanStringZeroTicksResult = dbo.ConvertToTimeSpanString(@timeSpanZeroTicks) SET @convertFromTimeSpanStringZeroTimeSpanResult = dbo.ConvertFromTimeSpanString(@timeSpanZeroString) SET @convertToTimeSpanStringMaxTicksResult = dbo.ConvertToTimeSpanString(@timeSpanMaxValueTicks) SET @convertFromTimeSpanStringMaxTimeSpanResult = dbo.ConvertFromTimeSpanString(@timeSpanMaxValueString) -- Test Results SELECT 'Convert to TimeSpan String from Ticks (Minimum)' AS Test , CASE WHEN @convertToTimeSpanStringMinTicksResult = @timeSpanMinValueString THEN 'Pass' ELSE 'Fail' END AS [Test Status] , @timeSpanMinValueTicks AS [Ticks] , CONVERT(varchar(26), NULL) AS [TimeSpan String] , CONVERT(varchar(26), @convertToTimeSpanStringMinTicksResult) AS [Actual Result] , CONVERT(varchar(26), @timeSpanMinValueString) AS [Expected Result] UNION ALL SELECT 'Convert from TimeSpan String to Ticks (Minimum)' AS Test , CASE WHEN @convertFromTimeSpanStringMinTimeSpanResult = @timeSpanMinValueTicks THEN 'Pass' ELSE 'Fail' END AS [Test Status] , NULL AS [Ticks] , @timeSpanMinValueString AS [TimeSpan String] , CONVERT(varchar(26), @convertFromTimeSpanStringMinTimeSpanResult) AS [Actual Result] , CONVERT(varchar(26), @timeSpanMinValueTicks) AS [Expected Result] UNION ALL SELECT 'Convert to TimeSpan String from Ticks (Zero)' AS Test , CASE WHEN @convertToTimeSpanStringZeroTicksResult = @timeSpanZeroString THEN 'Pass' ELSE 'Fail' END AS [Test Status] , @timeSpanZeroTicks AS [Ticks] , CONVERT(varchar(26), NULL) AS [TimeSpan String] , CONVERT(varchar(26), @convertToTimeSpanStringZeroTicksResult) AS [Actual Result] , CONVERT(varchar(26), @timeSpanZeroString) AS [Expected Result] UNION ALL SELECT 'Convert from TimeSpan String to Ticks (Zero)' AS Test , CASE WHEN @convertFromTimeSpanStringZeroTimeSpanResult = @timeSpanZeroTicks THEN 'Pass' ELSE 'Fail' END AS [Test Status] , NULL AS [Ticks] , @timeSpanZeroString AS [TimeSpan String] , CONVERT(varchar(26), @convertFromTimeSpanStringZeroTimeSpanResult) AS [Actual Result] , CONVERT(varchar(26), @timeSpanZeroTicks) AS [Expected Result] UNION ALL SELECT 'Convert to TimeSpan String from Ticks (Maximum)' AS Test , CASE WHEN @convertToTimeSpanStringMaxTicksResult = @timeSpanMaxValueString THEN 'Pass' ELSE 'Fail' END AS [Test Status] , @timeSpanMaxValueTicks AS [Ticks] , CONVERT(varchar(26), NULL) AS [TimeSpan String] , CONVERT(varchar(26), @convertToTimeSpanStringMaxTicksResult) AS [Actual Result] , CONVERT(varchar(26), @timeSpanMaxValueString) AS [Expected Result] UNION ALL SELECT 'Convert from TimeSpan String to Ticks (Maximum)' AS Test , CASE WHEN @convertFromTimeSpanStringMaxTimeSpanResult = @timeSpanMaxValueTicks THEN 'Pass' ELSE 'Fail' END AS [Test Status] , NULL AS [Ticks] , @timeSpanMaxValueString AS [TimeSpan String] , CONVERT(varchar(26), @convertFromTimeSpanStringMaxTimeSpanResult) AS [Actual Result] , CONVERT(varchar(26), @timeSpanMaxValueTicks) AS [Expected Result] -- Ticks Date Add Test PRINT 'Testing DateAddTicks...' DECLARE @DateAddTicksPositiveTicksResult datetimeoffset DECLARE @DateAddTicksZeroTicksResult datetimeoffset DECLARE @DateAddTicksNegativeTicksResult datetimeoffset SET @DateAddTicksPositiveTicksResult = dbo.DateAddTicks(@dateTimeOffsetMinMaxDiffTicks, @dateTimeOffsetMinValue) SET @DateAddTicksZeroTicksResult = dbo.DateAddTicks(@timeSpanZeroTicks, @dateTimeOffsetMinValue) SET @DateAddTicksNegativeTicksResult = dbo.DateAddTicks(@dateTimeOffsetMaxMinDiffTicks, @dateTimeOffsetMaxValue) -- Test Results SELECT 'Date Add with Ticks Test (Positive)' AS Test , CASE WHEN @DateAddTicksPositiveTicksResult = @dateTimeOffsetMaxValue THEN 'Pass' ELSE 'Fail' END AS [Test Status] , @dateTimeOffsetMinMaxDiffTicks AS [Ticks] , @dateTimeOffsetMinValue AS [Starting Date] , @DateAddTicksPositiveTicksResult AS [Actual Result] , @dateTimeOffsetMaxValue AS [Expected Result] UNION ALL SELECT 'Date Add with Ticks Test (Zero)' AS Test , CASE WHEN @DateAddTicksZeroTicksResult = @dateTimeOffsetMinValue THEN 'Pass' ELSE 'Fail' END AS [Test Status] , @timeSpanZeroTicks AS [Ticks] , @dateTimeOffsetMinValue AS [Starting Date] , @DateAddTicksZeroTicksResult AS [Actual Result] , @dateTimeOffsetMinValue AS [Expected Result] UNION ALL SELECT 'Date Add with Ticks Test (Negative)' AS Test , CASE WHEN @DateAddTicksNegativeTicksResult = @dateTimeOffsetMinValue THEN 'Pass' ELSE 'Fail' END AS [Test Status] , @dateTimeOffsetMaxMinDiffTicks AS [Ticks] , @dateTimeOffsetMaxValue AS [Starting Date] , @DateAddTicksNegativeTicksResult AS [Actual Result] , @dateTimeOffsetMinValue AS [Expected Result] -- Ticks Date Diff Test PRINT 'Testing Date Diff Ticks...' DECLARE @dateDiffTicksMinMaxResult bigint DECLARE @dateDiffTicksMaxMinResult bigint SET @dateDiffTicksMinMaxResult = dbo.DateDiffTicks(@dateTimeOffsetMinValue, @dateTimeOffsetMaxValue) SET @dateDiffTicksMaxMinResult = dbo.DateDiffTicks(@dateTimeOffsetMaxValue, @dateTimeOffsetMinValue) -- Test Results SELECT 'Date Difference in Ticks Test (Min, Max)' AS Test , CASE WHEN @dateDiffTicksMinMaxResult = @dateTimeOffsetMinMaxDiffTicks THEN 'Pass' ELSE 'Fail' END AS [Test Status] , @dateTimeOffsetMinValue AS [Starting Date] , @dateTimeOffsetMaxValue AS [Ending Date] , @dateDiffTicksMinMaxResult AS [Actual Result] , @dateTimeOffsetMinMaxDiffTicks AS [Expected Result] UNION ALL SELECT 'Date Difference in Ticks Test (Max, Min)' AS Test , CASE WHEN @dateDiffTicksMaxMinResult = @dateTimeOffsetMaxMinDiffTicks THEN 'Pass' ELSE 'Fail' END AS [Test Status] , @dateTimeOffsetMaxValue AS [Starting Date] , @dateTimeOffsetMinValue AS [Ending Date] , @dateDiffTicksMaxMinResult AS [Actual Result] , @dateTimeOffsetMaxMinDiffTicks AS [Expected Result] PRINT 'Tests Complete.' GO --- END Test Harness ---