Преобразование UTC Milliseconds в DATETIME на сервере SQL

Я хочу преобразовать миллисекунды UTC в DateTime на SQL-сервере.

Это легко сделать на С#, следуя следующему коду:

DateTime startDate = new DateTime(1970, 1, 1).AddMilliseconds(1348203320000);

Мне нужно сделать это на SQL-сервере. Я нашел здесь script здесь, но это началось с 1900-01-01.

Я использовал функцию DATEADD, как показано ниже, но это означало исключение арифметического переполнения, заставив миллисекунды как разницу:

SELECT DATEADD(MILLISECOND,1348203320000,'1970-1-1')

Как правильно выполнить преобразование?

Ответ 1

DECLARE @UTC BIGINT
SET @UTC = 1348203320997 

SELECT DATEADD(MILLISECOND, @UTC % 1000, DATEADD(SECOND, @UTC / 1000, '19700101'))

Ответ 2

Для DATEADD требуется целое число в качестве второго аргумента. Ваш номер 1348203320000 очень большой для целого числа, поэтому он вызывает ошибку во время выполнения. Вместо этого вы должны использовать тип bigint и предоставить DATEADD с правильными значениями int, разделив миллисекунды на секунды и миллисекунды. Это образец, который вы можете использовать.

DECLARE @total bigint = 1348203320000;

DECLARE @seconds int = @total / 1000
DECLARE @milliseconds int = @total % 1000;

DECLARE @result datetime = '1970-1-1';
SET @result = DATEADD(SECOND, @seconds,@result);
SET @result = DATEADD(MILLISECOND, @milliseconds,@result);
SELECT @result

Ответ 3

Ниже функции, которая преобразует миллисекунды в datetime

IF object_id('dbo.toDbTimeMSC', 'FN') IS NOT NULL DROP FUNCTION dbo.toDbTimeMSC
GO
CREATE FUNCTION [dbo].[toDbTimeMSC] (@unixTimeMSC BIGINT) RETURNS DATETIME
BEGIN
    RETURN DATEADD(MILLISECOND, @unixTimeMSC % 1000, DATEADD(SECOND, @unixTimeMSC / 1000, '19700101'))
END
GO

- выберите dbo.toDbTimeMSC(1348203320000)

Ответ 4

У меня были проблемы с использованием приведенных здесь ответов (особенно, что система подсчитывала тики формы 0001-01-01) - поэтому я сделал это:

CONVERT(DATETIME,[Time]/ 10000.0/1000/86400-693595)

--explanation for [Time_in_Ticks]/ 10000.0/1000/86400-693595
--Time is in "ticks"
--10000 = number of ticks in Milisecond
--1000  = number of milisecons in second
--86400 = number of seconds in a day (24hours*60minutes*60second)
--693595= number of days between 0001-01-01 and 1900-01-01 (which is base
--          date when converting from int to datetime)

Ответ 5

Используя SQL Server 2008R2, это обеспечило требуемый результат:

CAST(SWITCHOFFSET(CAST(dateadd(s, convert(bigint, [t_stamp]) / 1000, convert(datetime, '1-1-1970 00:00:00')) AS DATETIMEOFFSET), DATENAME (TZoffset, SYSDATETIMEOFFSET())) AS DATETIME)