Подтвердить что ты не робот

Преобразование объекта SQL Server DateTime в BIGINT (.Net ticks)

Мне нужно преобразовать значение типа DateTime в тип BIGINT в формате .Net-тиков (число интервалов 100-наносекунд, прошедших с 12:00 до полуночи, 1 января 0001 года).

Преобразование должно выполняться на сервере Sql 2008 с использованием запроса T-SQL

Например:

DateTime value - 12/09/2011 00:00:00

преобразуется в:

BIGINT value - 634513824000000000
4b9b3361

ОТВЕТЫ

Ответ 1

Я нашел статью CodeProject, которая может помочь: Преобразовать DateTime To.NET Ticks с помощью T-SQL

Я включаю функцию SQL из вышеприведенной статьи (надеюсь, это нормально? Поскольку это требует регистрации.)

CREATE FUNCTION [dbo].[MonthToDays365] (@month int)
RETURNS int
WITH SCHEMABINDING
AS
-- converts the given month (0-12) to the corresponding number of days into the year (by end of month)
-- this function is for non-leap years
BEGIN 
RETURN
    CASE @month
        WHEN 0 THEN 0
        WHEN 1 THEN 31
        WHEN 2 THEN 59
        WHEN 3 THEN 90
        WHEN 4 THEN 120
        WHEN 5 THEN 151
        WHEN 6 THEN 181
        WHEN 7 THEN 212
        WHEN 8 THEN 243
        WHEN 9 THEN 273
        WHEN 10 THEN 304
        WHEN 11 THEN 334
        WHEN 12 THEN 365
        ELSE 0
    END
END

GO

CREATE FUNCTION [dbo].[MonthToDays366] (@month int)
RETURNS int 
WITH SCHEMABINDING
AS
-- converts the given month (0-12) to the corresponding number of days into the year (by end of month)
-- this function is for leap years
BEGIN 
RETURN
    CASE @month
        WHEN 0 THEN 0
        WHEN 1 THEN 31
        WHEN 2 THEN 60
        WHEN 3 THEN 91
        WHEN 4 THEN 121
        WHEN 5 THEN 152
        WHEN 6 THEN 182
        WHEN 7 THEN 213
        WHEN 8 THEN 244
        WHEN 9 THEN 274
        WHEN 10 THEN 305
        WHEN 11 THEN 335
        WHEN 12 THEN 366
        ELSE 0
    END
END

GO

CREATE FUNCTION [dbo].[MonthToDays] (@year int, @month int)
RETURNS int
WITH SCHEMABINDING
AS
-- converts the given month (0-12) to the corresponding number of days into the year (by end of month)
-- this function is for non-leap years
BEGIN 
RETURN 
    -- determine whether the given year is a leap year
    CASE 
        WHEN (@year % 4 = 0) and ((@year % 100  != 0) or ((@year % 100 = 0) and (@year % 400 = 0))) THEN dbo.MonthToDays366(@month)
        ELSE dbo.MonthToDays365(@month)
    END
END

GO

CREATE FUNCTION [dbo].[TimeToTicks] (@hour int, @minute int, @second int)  
RETURNS bigint 
WITH SCHEMABINDING
AS 
-- converts the given hour/minute/second to the corresponding ticks
BEGIN 
RETURN (((@hour * 3600) + CONVERT(bigint, @minute) * 60) + CONVERT(bigint, @second)) * 10000000
END

GO

CREATE FUNCTION [dbo].[DateToTicks] (@year int, @month int, @day int)
RETURNS bigint
WITH SCHEMABINDING
AS
-- converts the given year/month/day to the corresponding ticks
BEGIN 
RETURN CONVERT(bigint, (((((((@year - 1) * 365) + ((@year - 1) / 4)) - ((@year - 1) / 100)) + ((@year - 1) / 400)) + dbo.MonthToDays(@year, @month - 1)) + @day) - 1) * 864000000000;
END

GO

CREATE FUNCTION [dbo].[DateTimeToTicks] (@d datetime)
RETURNS bigint
WITH SCHEMABINDING
AS
-- converts the given datetime to .NET-compatible ticks
-- see https://msdn.microsoft.com/en-us/library/system.datetime.ticks(v=vs.110).aspx
BEGIN 
RETURN 
    dbo.DateToTicks(DATEPART(yyyy, @d), DATEPART(mm, @d), DATEPART(dd, @d)) +
    dbo.TimeToTicks(DATEPART(hh, @d), DATEPART(mi, @d), DATEPART(ss, @d)) +
    (CONVERT(bigint, DATEPART(ms, @d)) * CONVERT(bigint,10000));
END

GO

Ответ 2

Я обсуждал, следует ли публиковать это, потому что это зависит от того, как даты хранятся на двоичном уровне в SQL Server, и поэтому это очень хрупкое решение. Для чего-то другого, кроме одноразового преобразования, я бы использовал что-то вроде ответа, опубликованного @Solution Evangelist. Тем не менее, вы можете найти это интересным в академическом стиле, поэтому я все равно отправлю его.

Использование того факта, что точность DateTime2 соответствует длительности тика в .NET и что оба они основаны на датах начала 01-01-0001 00:00:00.0000000, вы можете отбрасывать DateTime до DateTime2 и затем переведите его на binary(9): 0x07F06C999F3CB7340B

Информация о времени и времени хранится в RTL, поэтому для всплытия мы получаем 0x0B34B73C9F996CF007.

Первые три байта содержат количество дней с 01-01-0001, а следующие 5 байтов хранят отметки 100 нс с полуночи того дня, поэтому мы можем взять количество дней, умножить на тики в день и добавить тики, представляющие время, прошедшее за день.

Выполнение следующего кода:

set @date = getdate()
set @ticksPerDay = 864000000000

declare @date2 datetime2 = @date

declare @dateBinary binary(9) = cast(reverse(cast(@date2 as binary(9))) as binary(9))
declare @days bigint = cast(substring(@dateBinary, 1, 3) as bigint)
declare @time bigint = cast(substring(@dateBinary, 4, 5) as bigint)

select @date as [DateTime], @date2 as [DateTime2], @days * @ticksPerDay + @time as [Ticks]

возвращает следующие результаты:

DateTime                DateTime2              Ticks
----------------------- ---------------------- --------------------
2011-09-12 07:20:32.587 2011-09-12 07:20:32.58 634514088325870000

Взятие возвращаемого числа Ticks и преобразование обратно в DateTime в .NET:

DateTime dt = new DateTime(634514088325870000);
dt.ToString("yyyy-MM-dd HH:mm:ss.fffffff").Dump();

Возвращает дату с сервера sql:

2011-09-12 07:20:32.5870000

Ответ 3

Tsk,

Здесь рекомендация № 1 упрощена, хотя я не могу поверить, что это был лучший результат. Конечно, это встроено, но в этот момент все, что я нахожусь в крайнем сроке. Рефакторинг занял менее 1 минуты, но, возможно, это поможет другим, которые ищут решение с 1 остановкой.

CREATE FUNCTION [dbo].[Ticks] (@dt DATETIME)
RETURNS BIGINT
WITH SCHEMABINDING
AS
BEGIN 
DECLARE @year INT = DATEPART(yyyy, @dt)
DECLARE @month INT = DATEPART(mm, @dt)
DECLARE @day INT = DATEPART(dd, @dt)
DECLARE @hour INT = DATEPART(hh, @dt)
DECLARE @min INT = DATEPART(mi, @dt)
DECLARE @sec INT = DATEPART(ss, @dt)

DECLARE @days INT =
    CASE @month - 1
        WHEN 0 THEN 0
        WHEN 1 THEN 31
        WHEN 2 THEN 59
        WHEN 3 THEN 90
        WHEN 4 THEN 120
        WHEN 5 THEN 151
        WHEN 6 THEN 181
        WHEN 7 THEN 212
        WHEN 8 THEN 243
        WHEN 9 THEN 273
        WHEN 10 THEN 304
        WHEN 11 THEN 334
        WHEN 12 THEN 365
    END
    IF  @year % 4 = 0 AND (@year % 100  != 0 OR (@year % 100 = 0 AND @year % 400 = 0)) AND @month > 2 BEGIN
        SET @days = @days + 1
    END
RETURN CONVERT(bigint, 
    ((((((((@year - 1) * 365) + ((@year - 1) / 4)) - ((@year - 1) / 100)) + ((@year - 1) / 400)) + @days) + @day) - 1) * 864000000000) +
    ((((@hour * 3600) + CONVERT(bigint, @min) * 60) + CONVERT(bigint, @sec)) * 10000000) + (CONVERT(bigint, DATEPART(ms, @dt)) * CONVERT(bigint,10000));

END
GO

Ответ 4

вы можете использовать ниже sql для преобразования даты или utcdate в тики

declare @date datetime2 = GETUTCDATE() or getdate()
declare @dateBinary binary(9) = cast(reverse(cast(@date as binary(9))) as binary(9))
declare @days bigint = cast(substring(@dateBinary, 1, 3) as bigint)
declare @time bigint = cast(substring(@dateBinary, 4, 5) as bigint)

select @date as [DateTime],  @days * 864000000000 + @time as [Ticks]

и используйте ниже sql для преобразования отметки в дату

SELECT Converted = CAST(635324318540000000/864000000000.0 - 693595.0 AS DATETIME)

Ответ 5

РЕДАКТИРОВАТЬ: Обновлено из-за обновленных сведений об исходном вопросе

Чтобы обеспечить такую ​​точность в 100 нс, вы должны выполнить алгоритм на SQL Server 2008 с использованием нового типа даты - datetime2, точность которого составляет 100 наносекунд. Очевидно, что ссылка на сам алгоритм уже предоставлена ​​в другом сообщении.

Свойство DateTime.Ticks

Один тик представляет собой сто наносекунд или одну десятимиллионную второй. В миллисекундах насчитывается 10 000 тиков.

Значение этого свойства представляет собой число 100-наносекунд интервалы, которые прошли с 12:00 до полуночи, 1 января 0001 года, который представляет DateTime.MinValue. Он не включает количество тики, которые относятся к прыжкам секунд.

DateTime dateTime = DateTime.Now;
System.Int64 ticks = dateTime.Ticks;

Ответ 6

Более эффективное решение может быть: (обратите внимание, что первая строка предназначена только для тестирования)

DECLARE @YourDate DATETIME

SET @YourDate = GETDATE()

SELECT 
(
  (
  -- seconds since 1970
  CAST(DATEDIFF(s,'1970-01-01 12:00:00',@YourDate) As BIGINT)
  )
-- seconds from 0001 to 1970 (approximate)
+ 62125920000
) 
-- make those seconds nanoseconds
* 1000000000

Учитывая, что ваша дата ввода только сокращается до нескольких секунд, мы просто обрабатываем ее в секундах и умножаем на 1000000000, чтобы получить наносекунды.

Ответ 7

Я не получал миллисекундного однострочного решения этого вопроса, так что вот один из них:

SELECT ROUND(CAST(CAST(GETUTCDATE() AS FLOAT)*8.64e8 AS BIGINT),-1)*1000+599266080000000000

8.64e8 = TimeSpan.TicksPerDay/1000
599266080000000000 = DateTime.Parse('1900-01-01'). Ticks

Это работает для типа DATETIME, но не для DATETIME2. Разрешение DATETIME 4/3 мс требует включения ROUND (..., -1): после умножения на 8.64e8 результат поплавка всегда заканчивается 0 или 33.3 или 66.6. Это округляется до 0, 30 или 70.

Ответ 8

Эта функция возвращает время unix:

alter FUNCTION [dbo].[GETTICKS] (@datetime datetime)
RETURNS bigint
WITH SCHEMABINDING
AS
BEGIN 
    RETURN 
        DATEPART(millisecond,@datetime) +
        CONVERT(bigint, 
                    1000 * (
                        DATEPART(second,@datetime) +
                        60 * DATEPART(minute,@datetime) +
                        3600 * DATEPART(hour,@datetime) +
                        3600 * 24 * DATEPART(DAYOFYEAR,@datetime) +
                        convert(bigint, 3600 * 24 * (((DATEPART(year,@datetime) - 1970) * 365) + ((DATEPART(year,@datetime) - 1972) / 4)))
                ) )
END