Иногда нам приходится использовать T-SQL для вычисления времени, прошедшего между двумя событиями, значения даты и времени которых получены в качестве входных данных. Для некоторых целей достаточно вычислить разницу как длительность какой-нибудь части, такой как секунды, с использованием функции DATEDIFF или DATEDIFF_BIG. Первая возвращает разницу в желаемой части в виде четырехбайтового целого числа (INT); вторая (добавленная в SQL Server 2016) возвращает разницу как 8-байтовое целое число (BIGINT). Возникает сложность, особенно в версиях, предшествующих SQL Server 2016, когда разница не укладывается в возвращаемый тип, но ее не очень трудно устранить. Значительно сложнее вычислить разницу как комбинацию частей, начиная с годов и вплоть до наносекунд. Например, если мы имеем два входных значения даты и времени @t1 = '19710212 12:00:00.0000001' и @dt2 = '20170208 12:00:00.0000000', результатом вычислений должна стать разница 45 лет, 11 месяцев, 26 дней, 23 часа, 59 минут, 59 секунд и 999 999 900 наносекунд, с положительным знаком (так как @dt1 < @dt2).

В этой статье будет показано решение задачи в виде встроенной функции, возвращающей табличное значение (TVF) с именем DATEDIFFPARTS. Она принимает два входных значения DATETIME2, именуемых @dt1 и @dt2, и возвращает набор результатов с одной строкой, с одним столбцом для знака результата (1, когда @dt1 < @dt2, -1, когда @dt1 > @ft2, 0, когда они одинаковы, и NULL, если какое-нибудь значение равно NULL) и одним столбцом для каждой из частей.

Это довольно интересная задача, поэтому я рекомендую вам попытаться решить ее самостоятельно, прежде чем посмотреть мое решение. Используйте программный код для тестирования с двумя постоянными входными значениями, как на рисунке 1.

 

Входные значения
Рисунок 1. Входные значения

Предполагается, что выходные данные должны быть как на рисунке 2.

 

Выходные значения
Рисунок 2. Выходные значения

Встроенную функцию TVF можно применить к какой-нибудь таблице, содержащей разделители интервалов для вычисления разницы для каждого интервала, например, как в листинге 1.

Предполагается, что этот программный код выдаст выходные данные (разделенные на две части для ясности), как на рисунке 3. Желаю вам успеха!

 

Выходные значения функции
Рисунок 3. Выходные значения функции

Решение

В листинге 2 дано определение подготовленной мною функции DATEDIFFPARTS.

Я использую последовательность операторов CROSS APPLY, чтобы сделать элементы, созданные одним оператором, доступными последующим операторам, следуя семантике логической обработки запросов. Если вам необходимо освежить знания о том, как работает оператор APPLY, и об особенностях логической обработки запросов, нужную информацию можно найти в статье «Логическая обработка запросов: предложение FROM и оператор APPLY» (опубликована в Windows IT Pro/RE № 5 за 2016 год).

На первом шаге решения определяется производная таблица D со столбцами dt1, dt2 и sgn в предложении FROM (листинг 3).

Благодаря этому шагу, если два входных значения представляют собой различные точки времени, dt1 будет содержать более раннюю, а dt2 — более позднюю точку. Это важно для корректности вычислений. На основе столбца sgn можно определить знак результата (1, когда @dt1 раньше, чем @dt2; -1, когда @dt2 i раньше, чем @dt1; 0, если они одинаковы; NULL, если хотя бы одно значение равно NULL).

На втором шаге решения используется оператор CROSS APPLY, чтобы определить коррелированную производную таблицу A1 со столбцами t1, t2, yydiff, mmdiff и dddiff (листинг 4).

Столбцы t1 и t2 — просто части времени, извлеченные из dt1 и dt2 соответственно. Столбцы yydiff, mmdiff и dddiff показывают разницу между dt1 и dt2 в годах, месяцах и днях соответственно. Каждое из этих различий должно быть скорректировано путем вычитания одной единицы, если значение части, находящейся непосредственно ниже, в dt2 меньше, чем в dt1. Например, предположим, что первое значение год-месяц dt1-1971-02, а второе значение год-месяц dt2-2017-01. Нескорректированная разница года между dt1 и dt2 (yydiff) — 46. Но поскольку часть месяца в dt2 меньше, чем часть месяца в dt1, нужно вычесть один год из разницы лет, что дает 45 целых лет. На третьем шаге решения вычисляется значение коррекции для каждой из трех частей (листинг 5).

Оператор CROSS APPLY используется для того, чтобы определить коррелированную производную таблицу A2 со столбцами subyy, submm и subdd, которые содержат 1, если единица должна быть вычтена из соответствующей части, и 0 в противном случае. Используемый прием: добавить разницу, вычисленную на шаге 2, к dt1, и если результат больше dt2, то нужно скорректировать соответствующую часть, вычитая одну единицу. Например, если после добавления лет yydiff к dt1 результат больше dt2, subyy (корректировка года) равно 1, в противном случае 0.

На четвертом шаге с помощью оператора CROSS APPLY определяется коррелированная производная таблица с именем A3, содержащая столбец nsdiff (листинг 6).

Столбец nsdiff содержит разницу наносекунд между t1 и t2, скорректированную на число наносекунд в день (86 400 000 000 000), если значение subdd равно 1 (если t1 > t2). Для подсчета числа наносекунд в t1 и t2 число секунд в значении умножается на число наносекунд в одной секунде (1 000 000 000) и добавляется наносекундная часть значения. Вычисление проводится таким образом, чтобы обойти препятствие, которое заключается в том, что разница в наносекундах между t1 и t2 может не уместиться в 4-байтовом целом числе. Если вы работаете с SQL Server 2016 или более новой версией, приведенный в листинге 6 программный код можно упростить с использованием DATEDIFF_BIG (листинг 7).

Пятый и последний шаг — вычислить столбец результатов в списке SELECT:

SELECT
    sgn,
    yydiff - subyy AS yy,
    (mmdiff - submm) % 12 AS mm,
    DATEDIFF(day, DATEADD(mm, mmdiff -
    submm, dt1), dt2) - subdd AS dd,
    nsdiff / CAST(3600000000000 AS
    BIGINT) AS hh,
    nsdiff / CAST(60000000000 AS
    BIGINT) % 60 AS mi,
    nsdiff / 1000000000 % 60 AS ss,
    nsdiff % 1000000000 AS ns

Столбец sgn уже вычислен на шаге 1.

Окончательная разница лет (столбец результатов yy) представляет собой yydiff, скорректированный на subyy.

Окончательная разница месяцев (столбец результатов mm) представляет собой скорректированную месячную разницу (mmdiff — submm), по модулю 12, поскольку это месячная разница после учета годовой разницы.

Окончательная разница дней (столбец результатов dd) представляет собой разницу дней между значением dt1, измененным с учетом окончательной месячной разницы, и значением dt2, скорректированным на subdd.

Окончательная разница часов (столбец результатов hh) представляет собой разницу наносекунд (nsdiff), разделенную с применением целочисленного деления на число наносекунд в часе (3 600 000 000 000).

Окончательная разница минут (столбец результатов mi) представляет собой разницу наносекунд (nsdiff), разделенную с применением целочисленного деления на число наносекунд в минуте (60 000 000 000), по модулю 60, чтобы учесть часовую разницу.

Окончательная разница секунд (столбец результатов ss) представляет собой разницу наносекунд (nsdiff), разделенную с применением целочисленного деления на число наносекунд в секунде (1 000 000 000), по модулю 60, чтобы учесть разницу минут.

Окончательная разница наносекунд (столбец результатов ns) представляет собой разницу наносекунд (nsdiff), разделенную на число наносекунд в секунде (1 000 000 000), чтобы учесть разницу секунд.

Процесс вычисления даты и времени нередко оказывается сложным. В этой статье я представил решение для вычисления разницы между двумя значениями даты и времени в комбинации частей, охватывающих интервал от года до наносекунды. Помимо прочего, эта задача — хорошее упражнение в логике. Надеюсь, вы получили удовольствие от ее решения.

Листинг 1. Встроенная функция TVF 
SELECT dt1, dt2, sgn, yy, mm, dd, hh, mi, ss, ns
FROM ( VALUES('19710212 12:00:00.0000001', '20170208 12:00:00.0000000'),
             ('19710212 12:00:00.0000001', '19710212 12:00:00.0000001'),
             ('20170208 12:00:00.0000000', '19710212 12:00:00.0000001'),
             ('19710212 12:00:00.0000001', NULL) )
       AS D(dt1, dt2)
  CROSS APPLY dbo.DATEDIFFPARTS(dt1, dt2) AS F;
Листинг 2. Определение функции DATEDIFFPARTS 
IF OBJECT_ID(N'dbo.DATEDIFFPARTS', N'IF') IS NOT NULL
  DROP FUNCTION dbo.DATEDIFFPARTS;
GO
CREATE FUNCTION dbo.DATEDIFFPARTS(@dt1 AS DATETIME2, @dt2 AS DATETIME2)
  RETURNS TABLE
AS
RETURN
  SELECT
    sgn,
    yydiff - subyy AS yy,
    (mmdiff - submm) % 12 AS mm,
    DATEDIFF(day, DATEADD(mm, mmdiff - submm, dt1), dt2) - subdd AS dd,
    nsdiff / CAST(3600000000000 AS BIGINT) AS hh,
    nsdiff / CAST(60000000000 AS BIGINT) % 60 AS mi,
    nsdiff / 1000000000 % 60 AS ss,
    nsdiff % 1000000000 AS ns
  FROM ( VALUES( CASE WHEN @dt1 > @dt2 THEN @dt2 ELSE @dt1 END,
                 CASE WHEN @dt1 > @dt2 THEN @dt1 ELSE @dt2 END,
                 CASE WHEN @dt1 < @dt2 THEN 1
                      WHEN @dt1 = @dt2 THEN 0
                      WHEN @dt1 > @dt2 THEN -1 END ) ) AS D(dt1, dt2, sgn)
    CROSS APPLY ( VALUES( CAST(dt1 AS TIME), CAST(dt2 AS TIME),
                          DATEDIFF(yy, dt1, dt2),
                          DATEDIFF(mm, dt1, dt2),
                          DATEDIFF(dd, dt1, dt2) ) )
      AS A1(t1, t2, yydiff, mmdiff, dddiff)
    CROSS APPLY ( VALUES
        ( CASE WHEN DATEADD(yy, yydiff, dt1) > dt2 THEN 1 ELSE 0 END,
          CASE WHEN DATEADD(mm, mmdiff, dt1) > dt2 THEN 1 ELSE 0 END,
          CASE WHEN DATEADD(dd, dddiff, dt1) > dt2 THEN 1 ELSE 0 END ) )
      AS A2(subyy, submm, subdd)
    CROSS APPLY ( VALUES( CAST(86400000000000 AS BIGINT) * subdd
        + (CAST(1000000000 AS BIGINT) * DATEDIFF(ss, '00:00', t2)
             + DATEPART(ns, t2))
        - (CAST(1000000000 AS BIGINT) * DATEDIFF(ss, '00:00', t1)
             + DATEPART(ns, t1)) ) )
      AS A3(nsdiff);
GO
Листинг 3. Первый шаг решения
FROM ( VALUES( CASE WHEN @dt1 > @dt2 THEN @dt2 ELSE @dt1 END,
                 CASE WHEN @dt1 > @dt2 THEN @dt1 ELSE @dt2 END,
                 CASE WHEN @dt1 < @dt2 THEN 1
                      WHEN @dt1 = @dt2 THEN 0
                      WHEN @dt1 > @dt2 THEN -1 END ) ) AS D(dt1, dt2, sgn)
Листинг 4. Второй шаг решения
CROSS APPLY ( VALUES( CAST(dt1 AS TIME), CAST(dt2 AS TIME),
                          DATEDIFF(yy, dt1, dt2),
                          DATEDIFF(mm, dt1, dt2),
                          DATEDIFF(dd, dt1, dt2) ) )
      AS A1(t1, t2, yydiff, mmdiff, dddiff)
Листинг 5. Третий шаг решения
CROSS APPLY ( VALUES
        ( CASE WHEN DATEADD(yy, yydiff, dt1) > dt2 THEN 1 ELSE 0 END,
          CASE WHEN DATEADD(mm, mmdiff, dt1) > dt2 THEN 1 ELSE 0 END,
          CASE WHEN DATEADD(dd, dddiff, dt1) > dt2 THEN 1 ELSE 0 END ) )
      AS A2(subyy, submm, subdd)
Листинг 6. Четвертый шаг решения
    CROSS APPLY ( VALUES( CAST(86400000000000 AS BIGINT) * subdd
        + (CAST(1000000000 AS BIGINT) * DATEDIFF(ss, '00:00', t2)
             + DATEPART(ns, t2))
        - (CAST(1000000000 AS BIGINT) * DATEDIFF(ss, '00:00', t1)
             + DATEPART(ns, t1)) ) )
      AS A3(nsdiff)
Листинг 7. Усовершенствованный код листинга 6
CROSS APPLY ( VALUES( CAST(86400000000000 AS BIGINT) * subdd
        + DATEDIFF_BIG(ns, t1, t2) ) ) AS A3(nsdiff)