Comment puis-je convertir un bigint (horodatage UNIX) en datetime dans SQL Server?

Question

Comment puis-je convertir le timestamp UNIX (bigint) en DateTime dans SQL Server ?

Answer 1

Cela va le faire :

déclare @UNIX_TIME int
select @UNIX_TIME = 1111111111
-- Utilisation de dateadd pour ajouter des secondes à 1970-01-01
select [Datetime from UNIX Time] = dateadd(!précision!,@UNIX_TIME,'1970-01-01')

Au lieu de !précision!, utilisez : ss, ms ou mcs en fonction de la précision du timestamp. Le bigint est capable de conserver une précision en microsecondes.

Answer 2

Ajouter n secondes à 1970-01-01 vous donnera une date UTC car n – le timestamp Unix – est le nombre de secondes écoulées depuis 00:00:00 Temps Universel Coordonné (UTC), jeudi 1 janvier 1970.

Dans SQL Server 2016, vous pouvez convertir un fuseau horaire en un autre en utilisant AT TIME ZONE. Vous devez spécifier le nom du fuseau horaire au format standard Windows:

SELECT *
FROM (VALUES
    (1514808000),
    (1527854400)
) AS Tests(UnixTimestamp)
CROSS APPLY (SELECT DATEADD(SECOND, UnixTimestamp, '1970-01-01') AT TIME ZONE 'UTC') AS CA1(UTCDate)
CROSS APPLY (SELECT UTCDate AT TIME ZONE 'Heure normale du Pacifique') AS CA2(LocalDate)

UnixTimestamp

UTCDate

LocalDate

1514808000

2018-01-01 12:00:00 +00:00

2018-01-01 04:00:00 -08:00

1527854400

2018-06-01 12:00:00 +00:00

2018-06-01 05:00:00 -07:00

Ou simplement:

SELECT *, DATEADD(SECOND, UnixTimestamp, '1970-01-01')
              AT TIME ZONE 'UTC'
              AT TIME ZONE 'Heure normale du Pacifique' AS LocalDate
FROM (VALUES
    (1514808000),
    (1527854400)
) AS Tests(UnixTimestamp)

UnixTimestamp

LocalDate

1514808000

2018-01-01 04:00:00 -08:00

1527854400

2018-06-01 05:00:00 -07:00

Remarques:

• La conversion tient compte de l'heure d'été. Le fuseau horaire du Pacifique était UTC-08:00 en janvier 2018 et UTC-07:00 en juin 2018.
• Vous pouvez supprimer les informations sur le fuseau horaire en convertissant DATETIMEOFFSET en DATETIME.
• Pour une liste complète des "noms" des fuseaux horaires Windows, consultez la vue sys.time_zone_info ou utilisez tzutil.

Answer 3

Si le temps est en millisecondes et qu'il est nécessaire de les conserver :

DECLARE @value VARCHAR(32) = '1561487667713';

SELECT DATEADD(MILLISECOND, CAST(RIGHT(@value, 3) AS INT) - DATEDIFF(MILLISECOND,GETDATE(),GETUTCDATE()), DATEADD(SECOND, CAST(LEFT(@value, 10) AS INT), '1970-01-01T00:00:00'))

Answer 4

J'ai également dû faire face à ce problème. Malheureusement, aucune des réponses (ici et sur des dizaines d'autres pages) ne m'a été satisfaisante, car je ne peux toujours pas atteindre les dates au-delà de l'année 2038 en raison de conversions en entiers sur 32 bits quelque part.

Une solution qui a fonctionné pour moi a finalement été d'utiliser des variables de type float, ce qui m'a permis d'avoir au moins une date maximale de 2262-04-11T23:47:16.854775849. Cela ne couvre toujours pas l'ensemble du domaine des datetime, mais c'est suffisant pour mes besoins et peut aider d'autres personnes rencontrant le même problème.

-- variables de date
declare @ts bigint; -- estampille temporelle sur 64 bits, précision de 100ns
declare @d datetime2(7) = GETUTCDATE(); -- 'maintenant'
-- select @d = '2262-04-11T23:47:16.854775849'; -- ceci serait la date maximale

-- constantes:
declare @epoch datetime2(7) = cast('1970-01-01T00:00:00' as datetime2(7));
declare @epochdiff int = 25567; -- = jours entre le 1900-01-01 et le 1970-01-01
declare @ticksofday bigint = 864000000000; -- = (24*60*60*1000*1000*10)

-- variables d'aide:
declare @datepart float;
declare @timepart float;
declare @restored datetime2(7);

-- algorithme:
select @ts = DATEDIFF_BIG(NANOSECONDE, @epoch, @d) / 100; -- 'maintenant' en ticks selon l'époque Unix
select @timepart = (@ts % @ticksofday) / @ticksofday; -- extraire la partie horaire et la mettre à l'échelle en partie fractionnaire (par exemple, 1 heure est 1/24 d'une journée)
select @datepart = (@ts - @timepart) / @ticksofday; -- extraire la partie date et la mettre à l'échelle en partie fractionnaire
select @restored = cast(@epochdiff + @datepart + @timepart as datetime); -- reconstruire les parties en une valeur datetime

-- interroger la date d'origine, le timestamp intermédiaire et la date restaurée pour comparaison
select
  @d original,
  @ts unix64,
  @restored restaurée
;

-- exemple de résultat pour la date maximale:
-- +-----------------------------+-------------------+-----------------------------+
-- | original                    | unix64            | restaurée                  |
-- +-----------------------------+-------------------+-----------------------------+
-- | 2262-04-11 23:47:16.8547758 | 92233720368547758 | 2262-04-11 23:47:16.8533333 |
-- +-----------------------------+-------------------+-----------------------------+

Il y a quelques points à considérer :

• La précision de 100ns est la nécessité dans mon cas, cependant cela semble être la résolution standard pour les estampilles temporelles Unix sur 64 bits. Si vous utilisez une autre résolution, vous devez ajuster @ticksofday et la première ligne de l'algorithme en conséquence.
• J'utilise d'autres systèmes qui ont leurs problèmes avec les fuseaux horaires etc. et j'ai trouvé que la meilleure solution pour moi serait toujours d'utiliser l'UTC. Pour vos besoins, cela peut être différent.
• 1900-01-01 est la date d'origine pour datetime2, tout comme l'époque 1970-01-01 pour les estampilles temporelles Unix.
• Les floats m'ont aidé à résoudre le problème de l'année 2038 et les débordements d'entiers et autres, mais gardez à l'esprit que les nombres à virgule flottante ne sont pas très performants et peuvent ralentir le traitement d'un grand nombre d'estampilles temporelles. De plus, les floats peuvent entraîner une perte de précision due à des erreurs d'arrondi, comme vous pouvez le voir dans la comparaison des résultats de l'exemple pour la date maximale ci-dessus (ici, l'erreur est d'environ 1,4425 ms).
• Dans la dernière ligne de l'algorithme, il y a une conversion en datetime. Malheureusement, une conversion explicite de valeurs numériques en datetime2 n'est pas autorisée, mais il est autorisé de convertir des numériques en datetime de manière explicite et cela, à son tour, est converti implicitement en datetime2. Cela peut être correct, pour le moment, mais cela peut changer dans les futures versions de SQL Server : Soit il y aura une fonction dateadd_big() soit la conversion explicite en datetime2 sera autorisée ou la conversion explicite en datetime sera interdite, donc cela peut soit ne plus fonctionner ou il pourrait y avoir une méthode plus simple un jour.

Answer 5

@DanielLittle a la réponse la plus simple et la plus élégante à la question spécifique. Cependant, si vous êtes intéressé par la conversion dans un fuseau horaire spécifique ET en tenant compte de l'heure d'été (DST), voici ce qui fonctionne bien :

CAST(DATEADD(S, [UnixTimestamp], '1970-01-01') AT TIME ZONE 'UTC' AT TIME ZONE 'Heure normale du Pacifique' AS Datetime)

Remarque : Cette solution ne fonctionne que sur SQL Server 2016 et supérieur (et Azure).

Pour créer une fonction :

CREATE FUNCTION dbo.ConvertUnixTime (@input INT)
RETURNS Datetime
AS BEGIN
    DECLARE @Unix Datetime

    SET @Unix = CAST(DATEADD(S, @Input, '1970-01-01') AT TIME ZONE 'UTC' AT TIME ZONE 'Heure normale du Pacifique' AS Datetime)

    RETURN @Unix
END

Vous pouvez appeler la fonction de la manière suivante :

SELECT   dbo.ConvertUnixTime([UnixTimestamp])
FROM     YourTable