¿Cómo puedo convertir bigint (indicación de fecha y hora UNIX) a fecha y hora en SQL Server?

¿Cómo puedo convertir bigint (indicación de hora UNIX) en Datetime en SQL Server?

tratar:

CREATE FUNCTION dbo.fn_ConvertToDateTime (@Datetime BIGINT) RETURNS DATETIME AS BEGIN DECLARE @LocalTimeOffset BIGINT ,@AdjustedLocalDatetime BIGINT; SET @LocalTimeOffset = DATEDIFF(second,GETDATE(),GETUTCDATE()) SET @AdjustedLocalDatetime = @Datetime - @LocalTimeOffset RETURN (SELECT DATEADD(second,@AdjustedLocalDatetime, CAST('1970-01-01 00:00:00' AS datetime))) END; GO 

Esto funcionó para mí:

 Select dateadd(S, [unixtime], '1970-01-01') From [Table] 

En caso de que alguien se pregunte por qué 1970-01-01, esto se llama Epoch time.Below es una cita de wikipedia

la cantidad de segundos transcurridos desde las 00:00:00 hora universal coordinada (UTC), jueves, 1 de enero de 1970, [1] [nota 1] sin contar los segundos intercalares

Me gusta esto

agregue la fecha y hora de Unix (época) a la fecha base en segundos

esto lo conseguirá por ahora (2010-05-25 07: 56: 23.000)

  SELECT dateadd(s,1274756183,'19700101 05:00:00:000') 

Si quieres ir al revés, echa un vistazo a este http://wiki.lessthandot.com/index.php/Epoch_Date

Esto lo hará:

 declare @UNIX_TIME int select @UNIX_TIME = 1111111111 -- Using dateadd to add seconds to 1970-01-01 select [Datetime from UNIX Time] = dateadd(!precision!,@UNIX_TIME,'1970-01-01') 

En lugar de! Precisión! use: ss, ms o mcs de acuerdo con la precisión de la marca de tiempo. Bigint es capaz de mantener una precisión de microsegundos.

Si alguien obtiene ” Error de desbordamiento aritmético convirtiendo la expresión a tipo de datos int ” debido a la marca de tiempo de unix está en letra grande (en lugar de int), puede usar esto:

SELECT DATEADD(S, CONVERT(int,LEFT(1462924862735870900, 10)), '1970-01-01') FROM TABLE

Reemplace la marca de tiempo codificada para su columna actual con sello de tiempo de Unix

Fuente: MSSQL bigint Marca de tiempo de Unix para fecha y hora con milisegundos

Prueba esto:

 SELECT from_unixtime(timestamp) FROM your_table 

http://www.w3resource.com/mysql/date-and-time-functions/mysql-from_unixtime-function.php

Esto se basa en el trabajo que Daniel Little hizo para esta pregunta, pero teniendo en cuenta el horario de verano (funciona para las fechas 01-01 1902 y mayor debido al límite int de la función de fecha y hora):

Primero tenemos que crear una tabla que almacene los intervalos de fechas para el horario de verano (fuente: Historial de tiempo en los Estados Unidos ):

 CREATE TABLE [dbo].[CFG_DAY_LIGHT_SAVINGS_TIME]( [BEGIN_DATE] [datetime] NULL, [END_DATE] [datetime] NULL, [YEAR_DATE] [smallint] NULL ) ON [PRIMARY] GO INSERT INTO CFG_DAY_LIGHT_SAVINGS_TIME VALUES ('2001-04-01 02:00:00.000', '2001-10-27 01:59:59.997', 2001), ('2002-04-07 02:00:00.000', '2002-10-26 01:59:59.997', 2002), ('2003-04-06 02:00:00.000', '2003-10-25 01:59:59.997', 2003), ('2004-04-04 02:00:00.000', '2004-10-30 01:59:59.997', 2004), ('2005-04-03 02:00:00.000', '2005-10-29 01:59:59.997', 2005), ('2006-04-02 02:00:00.000', '2006-10-28 01:59:59.997', 2006), ('2007-03-11 02:00:00.000', '2007-11-03 01:59:59.997', 2007), ('2008-03-09 02:00:00.000', '2008-11-01 01:59:59.997', 2008), ('2009-03-08 02:00:00.000', '2009-10-31 01:59:59.997', 2009), ('2010-03-14 02:00:00.000', '2010-11-06 01:59:59.997', 2010), ('2011-03-13 02:00:00.000', '2011-11-05 01:59:59.997', 2011), ('2012-03-11 02:00:00.000', '2012-11-03 01:59:59.997', 2012), ('2013-03-10 02:00:00.000', '2013-11-02 01:59:59.997', 2013), ('2014-03-09 02:00:00.000', '2014-11-01 01:59:59.997', 2014), ('2015-03-08 02:00:00.000', '2015-10-31 01:59:59.997', 2015), ('2016-03-13 02:00:00.000', '2016-11-05 01:59:59.997', 2016), ('2017-03-12 02:00:00.000', '2017-11-04 01:59:59.997', 2017), ('2018-03-11 02:00:00.000', '2018-11-03 01:59:59.997', 2018), ('2019-03-10 02:00:00.000', '2019-11-02 01:59:59.997', 2019), ('2020-03-08 02:00:00.000', '2020-10-31 01:59:59.997', 2020), ('2021-03-14 02:00:00.000', '2021-11-06 01:59:59.997', 2021), ('2022-03-13 02:00:00.000', '2022-11-05 01:59:59.997', 2022), ('2023-03-12 02:00:00.000', '2023-11-04 01:59:59.997', 2023), ('2024-03-10 02:00:00.000', '2024-11-02 01:59:59.997', 2024), ('2025-03-09 02:00:00.000', '2025-11-01 01:59:59.997', 2025), ('1967-04-30 02:00:00.000', '1967-10-29 01:59:59.997', 1967), ('1968-04-28 02:00:00.000', '1968-10-27 01:59:59.997', 1968), ('1969-04-27 02:00:00.000', '1969-10-26 01:59:59.997', 1969), ('1970-04-26 02:00:00.000', '1970-10-25 01:59:59.997', 1970), ('1971-04-25 02:00:00.000', '1971-10-31 01:59:59.997', 1971), ('1972-04-30 02:00:00.000', '1972-10-29 01:59:59.997', 1972), ('1973-04-29 02:00:00.000', '1973-10-28 01:59:59.997', 1973), ('1974-01-06 02:00:00.000', '1974-10-27 01:59:59.997', 1974), ('1975-02-23 02:00:00.000', '1975-10-26 01:59:59.997', 1975), ('1976-04-25 02:00:00.000', '1976-10-31 01:59:59.997', 1976), ('1977-04-24 02:00:00.000', '1977-10-31 01:59:59.997', 1977), ('1978-04-30 02:00:00.000', '1978-10-29 01:59:59.997', 1978), ('1979-04-29 02:00:00.000', '1979-10-28 01:59:59.997', 1979), ('1980-04-27 02:00:00.000', '1980-10-26 01:59:59.997', 1980), ('1981-04-26 02:00:00.000', '1981-10-25 01:59:59.997', 1981), ('1982-04-25 02:00:00.000', '1982-10-25 01:59:59.997', 1982), ('1983-04-24 02:00:00.000', '1983-10-30 01:59:59.997', 1983), ('1984-04-29 02:00:00.000', '1984-10-28 01:59:59.997', 1984), ('1985-04-28 02:00:00.000', '1985-10-27 01:59:59.997', 1985), ('1986-04-27 02:00:00.000', '1986-10-26 01:59:59.997', 1986), ('1987-04-05 02:00:00.000', '1987-10-25 01:59:59.997', 1987), ('1988-04-03 02:00:00.000', '1988-10-30 01:59:59.997', 1988), ('1989-04-02 02:00:00.000', '1989-10-29 01:59:59.997', 1989), ('1990-04-01 02:00:00.000', '1990-10-28 01:59:59.997', 1990), ('1991-04-07 02:00:00.000', '1991-10-27 01:59:59.997', 1991), ('1992-04-05 02:00:00.000', '1992-10-25 01:59:59.997', 1992), ('1993-04-04 02:00:00.000', '1993-10-31 01:59:59.997', 1993), ('1994-04-03 02:00:00.000', '1994-10-30 01:59:59.997', 1994), ('1995-04-02 02:00:00.000', '1995-10-29 01:59:59.997', 1995), ('1996-04-07 02:00:00.000', '1996-10-27 01:59:59.997', 1996), ('1997-04-06 02:00:00.000', '1997-10-26 01:59:59.997', 1997), ('1998-04-05 02:00:00.000', '1998-10-25 01:59:59.997', 1998), ('1999-04-04 02:00:00.000', '1999-10-31 01:59:59.997', 1999), ('2000-04-02 02:00:00.000', '2000-10-29 01:59:59.997', 2000) GO 

Ahora creamos una función para cada zona horaria estadounidense. Esto supone que el tiempo de Unix está en milisegundos. Si está en segundos, elimine / 1000 del código:

Pacífico

 create function [dbo].[UnixTimeToPacific] (@unixtime bigint) returns datetime as begin declare @pacificdatetime datetime declare @interimdatetime datetime = dateadd(s, @unixtime/1000, '1970-01-01') select @pacificdatetime = dateadd(hour,case when @interimdatetime between begin_date and end_date then -7 else -8 end ,@interimdatetime) from cfg_day_light_savings_time where year_date = datepart(year,@interimdatetime) if @pacificdatetime is null select @pacificdatetime= dateadd(hour, -7, @interimdatetime) return @pacificdatetime end 

Oriental

 create function [dbo].[UnixTimeToEastern] (@unixtime bigint) returns datetime as begin declare @easterndatetime datetime declare @interimdatetime datetime = dateadd(s, @unixtime/1000, '1970-01-01') select @easterndatetime = dateadd(hour,case when @interimdatetime between begin_date and end_date then -4 else -5 end ,@interimdatetime) from cfg_day_light_savings_time where year_date = datepart(year,@interimdatetime) if @easterndatetime is null select @easterndatetime= dateadd(hour, -4, @interimdatetime) return @easterndatetime end 

Central

 create function [dbo].[UnixTimeToCentral] (@unixtime bigint) returns datetime as begin declare @centraldatetime datetime declare @interimdatetime datetime = dateadd(s, @unixtime/1000, '1970-01-01') select @centraldatetime = dateadd(hour,case when @interimdatetime between begin_date and end_date then -5 else -6 end ,@interimdatetime) from cfg_day_light_savings_time where year_date = datepart(year,@interimdatetime) if @centraldatetime is null select @centraldatetime= dateadd(hour, -5, @interimdatetime) return @centraldatetime end 

Montaña

 create function [dbo].[UnixTimeToMountain] (@unixtime bigint) returns datetime as begin declare @mountaindatetime datetime declare @interimdatetime datetime = dateadd(s, @unixtime/1000, '1970-01-01') select @mountaindatetime = dateadd(hour,case when @interimdatetime between begin_date and end_date then -6 else -7 end ,@interimdatetime) from cfg_day_light_savings_time where year_date = datepart(year,@interimdatetime) if @mountaindatetime is null select @mountaindatetime= dateadd(hour, -6, @interimdatetime) return @mountaindatetime end 

Hawai

 create function [dbo].[UnixTimeToHawaii] (@unixtime bigint) returns datetime as begin declare @hawaiidatetime datetime declare @interimdatetime datetime = dateadd(s, @unixtime/1000, '1970-01-01') select @hawaiidatetime = dateadd(hour,-10,@interimdatetime) from cfg_day_light_savings_time where year_date = datepart(year,@interimdatetime) return @hawaiidatetime end 

Arizona

 create function [dbo].[UnixTimeToArizona] (@unixtime bigint) returns datetime as begin declare @arizonadatetime datetime declare @interimdatetime datetime = dateadd(s, @unixtime/1000, '1970-01-01') select @arizonadatetime = dateadd(hour,-7,@interimdatetime) from cfg_day_light_savings_time where year_date = datepart(year,@interimdatetime) return @arizonadatetime end 

Alaska

 create function [dbo].[UnixTimeToAlaska] (@unixtime bigint) returns datetime as begin declare @alaskadatetime datetime declare @interimdatetime datetime = dateadd(s, @unixtime/1000, '1970-01-01') select @alaskadatetime = dateadd(hour,case when @interimdatetime between begin_date and end_date then -8 else -9 end ,@interimdatetime) from cfg_day_light_savings_time where year_date = datepart(year,@interimdatetime) if @alaskadatetime is null select @alaskadatetime= dateadd(hour, -8, @interimdatetime) return @alaskadatetime end 

¿Mejor? Esta función convierte unixtime en milisegundos a datetime. Ha perdido milisegundos, pero sigue siendo muy útil para filtrar.

 CREATE FUNCTION [dbo].[UnixTimestampToGMTDatetime] (@UnixTimestamp bigint) RETURNS datetime AS BEGIN DECLARE @GMTDatetime datetime select @GMTDatetime = CASE WHEN dateadd(ss, @UnixTimestamp/1000, '1970-01-01') BETWEEN Convert(DATETIME, Convert(VARCHAR(4), Year(dateadd(ss, @UnixTimestamp/1000, '1970-01-01') )) + '-03-' + Convert(VARCHAR(2), (31 - (5 * Year(dateadd(ss, @UnixTimestamp/1000, '1970-01-01') )/4 + 4) % 7)) + ' 01:00:00', 20) AND Convert(DATETIME, Convert(VARCHAR(4), Year(dateadd(ss, @UnixTimestamp/1000, '1970-01-01') )) + '-10-' + Convert(VARCHAR(2), (31 - (5 * Year(dateadd(ss, @UnixTimestamp/1000, '1970-01-01') )/4 + 1) % 7)) + ' 02:00:00', 20) THEN Dateadd(hh, 1, dateadd(ss, @UnixTimestamp/1000, '1970-01-01')) ELSE Dateadd(hh, 0, dateadd(ss, @UnixTimestamp/1000, '1970-01-01')) END RETURN @GMTDatetime END