.net - small - ¿Cuál es el tipo de SQL correcto para almacenar un.pan Timespan con valores> 24:00:00?

timespan sql server 2008

Question

Estoy intentando almacenar .Net TimeSpan en SQL Server 2008 R2.

El primer código de EF parece sugerir que debe almacenarse como un Time(7) en SQL.

Sin embargo, TimeSpan en .Net puede manejar períodos más largos de 24 horas.

¿Cuál es la mejor manera de gestionar el almacenamiento de .Net TimeSpan en el servidor SQL?

Answer 1

Gracias por el consejo. Como no hay un equivalente en el servidor SQL. Simplemente creé un segundo campo que convirtió TimeSpan en ticks y lo guardé en DB. Luego evité almacenar el TimeSpan

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

Answer 2

Lo almacenaba en la base de datos como BIGINT y almacenaba el número de tics (por ejemplo, la propiedad TimeSpan.Ticks ).

De esa forma, si quisiera obtener un objeto TimeSpan cuando lo recupere, podría hacer TimeSpan.FromTicks(value) cual sería fácil.

Answer 3

No hay un equivalente directo. Simplemente almacénelo numéricamente, por ejemplo, el número de segundos o algo apropiado para su precisión requerida.

Answer 4

Normalmente, almaceno un TimeSpan como un bigint poblado con ticks desde la propiedad TimeSpan.Ticks como se sugirió anteriormente. También puede almacenar un TimeSpan como un varchar (26) poblado con la salida de TimeSpan.ToString (). Las cuatro funciones escalares (ConvertFromTimeSpanString, ConvertToTimeSpanString, DateAddTicks, DateDiffTicks) que escribí son útiles para manejar TimeSpan en el lado de SQL y evitar los ataques que producirían rangos acotados artificialmente. Si puede almacenar el intervalo en .NET TimeSpan en absoluto, también debería funcionar con estas funciones. Además, las funciones le permiten trabajar con TimeSpans y tics de 100 nanosegundos incluso cuando utiliza tecnologías que no incluyen .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 ---

Answer 5

Para ser coherente con lo que probablemente sea la fuente más probable de generación de un lapso de tiempo (calculando la diferencia de 2 veces o de fecha y hora), es posible que desee almacenar .NET TimeSpan como tipo de fecha y hora de SQL Server.

Esto se debe a que en SQL Server, la diferencia de 2 DateTime ( Cast in Float y luego Cast back to DateTime ) es simplemente un DateTime relativo al 1 de enero de 1900. Ex. Una diferencia de +0.1 segundos sería el 1 de enero de 1900 00: 00: 00.100 y -0.1 segundos sería el 31 de diciembre de 1899 23: 59: 59.900.

Para convertir .NET TimeSpan a SQL Server DateTime Type, primero debe convertirlo a .NET DateTime Type agregándolo a DateTime del 1 de enero de 1900. Por supuesto, cuando lo lee en .NET desde SQL Server , primero lo leería en .NET DateTime y luego restaría el 1 de enero de 1900 para convertirlo a .NET TimeSpan .

Para casos de uso donde los intervalos de tiempo se generan desde SQL Server DateTime y dentro de SQL Server (es decir, a través de T-SQL) y SQL Server es anterior a 2016, dependiendo de su rango y necesidades de precisión, puede no ser práctico almacenar ellos como milisegundos (sin mencionar Ticks ) porque el Tipo Int devuelto por DateDiff (frente a BigInt del BigInt de SS 2016 +) se desborda después de ~ 24 días en milisegundos y ~ 67 años. de segundos. Mientras que, esta solución manejará períodos de tiempo con precisión de hasta 0.1 segundos y de -147 a +8,099 años.

ADVERTENCIAS:

1. Esto solo funcionaría si la diferencia relativa al 1 de enero de 1900 daría como resultado un valor dentro del rango de un tipo de fecha y hora de SQL Server (1 de enero de 1753 a 31 de diciembre de 9999, también conocido como -147 a +8,099 años). No tenemos que preocuparnos tanto en el lado TimeSpan , ya que puede contener ~ 29 k a +29 k años. No mencioné el tipo DateTime2 SQL Server (cuyo rango, en el lado negativo, es mucho mayor que el de SQL Server DateTime ), porque: a) no se puede convertir a un valor numérico mediante un simple Cast yb) DateTime '' El rango s debería ser suficiente para la gran mayoría de los casos de uso.

2. Las diferencias de SQL Server DateTime calculadas a través del método Cast - to - Float - y - back no parecen ser precisas más allá de 0.1 segundos.

Answer 6

Sé que esta es una vieja pregunta, pero quería asegurarme de que se mencionan algunas otras opciones.

Dado que no puede almacenar un TimeSpan mayor a 24 horas en un campo de tipo de datos time sql; un par de otras opciones podrían ser.

1. Use un varchar (xx) para almacenar el ToString del TimeSpan. El beneficio de esto es que la precisión no tiene que ser cocida en el tipo de datos o el cálculo, (segundos vs milisegundos vs días vs quincenas) Todo lo que necesita hacer es utilizar TimeSpan.Parse / TryParse. Esto es lo que haría.

2. Use una segunda fecha, fecha y hora o fecha de salida, que almacena el resultado de la primera fecha + intervalo de tiempo. Leer desde el DB es una cuestión de TimeSpan x = SecondDate - FirstDate. El uso de esta opción lo protegerá para que otras bibliotecas de acceso a datos que no sean .NET accedan a los mismos datos pero no comprendan TimeSpans; en caso de que tengas un ambiente así.

Answer 7

Si no tiene que almacenar más de 24 horas, puede simplemente almacenar el tiempo , ya que SQL Server 2008 y posterior la asignación es

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

No se necesitan conversiones si solo necesita almacenar 24 horas o menos.

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

Pero , si desea almacenar más de 24 horas, necesitará almacenarlo en ticks, recuperar los datos y luego convertirlos a TimeSpan. Por ejemplo

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