Программисты, работающие с T-SQL, сталкиваются с неожиданно сложной проблемой: как правильно вычислить разницу между двумя значениями дат и времени. SQL Server не предоставляет встроенное решение этой задачи. Поэтому необходимо создать собственное.
Хотя функция DATEDIFF SQL Server позволяет вычислить разницу между двумя значениями даты и времени в единицах определенного элемента (т.е. год, месяц, день), она учитывает только определенный элемент и вышестоящие элементы во временной иерархии - но не нижестоящие элементы. Например, если установлен элемент дня, SQL Server будет считать год, месяц и день и игнорировать нижестоящие элементы (час, минута, секунда, миллисекунда). Следующий запрос позволяет установить разницу в годах между двумя значениями, разница между которыми 3 мсек.
SELECT DATEDIFF(year, '20051231 23:59:59.997',
'20060101 00:00:00.000')
SQL Server возвращает ложное значение разности лет 1 (вместо 0) так как функция не проверяет единицы измерения времени меньшие года, но легко вычитает 2005 из 2006.
Посмотрим, как получить правильную разность дат, принимая во внимание все единицы. Другими словами, заданы две временных метки - from_ts и to_ts. Посмотрим, как получить разницу между ними в единицах лет, месяцев, дней, часов, минут, секунд и миллисекунд.
Сначала проверьте свое решение
Начните решение задачи на определение разницы между датами с выполнения программного кода в Листинге 1. Код наполняет таблицу TimeStamps. Заполните ее примерными данными. В Таблице 1 показано содержание таблицы Time-Stamps. Каждая строка содержит целочисленный ключ (keycol) и пару временных меток (from_ts и to_ts ). Задача состоит в том, чтобы вычислить разницу между временными парами, учитывая все возможные элементы времени, и получить результат, показанный в Таблице 2.
Перед тем как посмотреть на мое решение, попытайтесь решить задачу сами. Решение должно обрабатывать варианты, в которых метка from_ts содержит более позднюю дату, чем метка to_ts. В этом случае программный код должен создать негативный результат. Результирующий столбец sgn должен идентифицировать позитивный (1) или негативный (-1) результат. Для экономии места в таблицу среди примерных выходных данных не вписаны значения меток from_ts и to_ts. Эти значения можно вставить обратно в таблицу TimeStamps, которая базируется на keycol.
Пошаговые вычисления
Перейдем к моему решению, которое показано в Листинге 2. Сначала рассмотрим внутренний запрос в метке F Листинга 2. Он создает вторичную таблицу D1, показанную в Таблице 3. Цель запроса - поместить большее значение пары временных меток (from_ts, to_ts) в результирующем столбце to_ts и разместить меньшее значение в from_ts. Для этого используются простые выражения CASE. Данная операция предусматривает в дальнейшем упрощение расчетов, гарантируя, что дата в метке from_ts - более ранняя или такая же, как в метке to_ts. Результирующий столбец sgn будет содержать значение 1, если from_ts меньше или равно to_ts и -1, если from_ts больше, чем to_ts.
Метка Е показывает запрос, который принимает D1, в качестве своих входных данных, а затем создает вторичную таблицу D2, которая показана в Таблице 4. Запрос использует функцию DATEDIFF() для вычисления разностей элементов дат (т.е год, месяц, день). Следует помнить о том, что разность элементов, которую вычисляет DATEDIFF(), может иметь отклонение от правильного значения разности на 1. Это происходит из-за того, что функция не учитывает элементы, стоящие во временной иерархии ниже заданного элемента. Следующий шаг будет контролировать это отклонение.
Запрос в метке D принимает D2 в качестве его входных данных и создает вторичную таблицу D3, которая показана в Таблице 5. Этот запрос использует выражение CASE для каждого элемента даты. Он прибавляет к значению метки from_ts соответственную разность элементов, полученную из предыдущего запроса. Если результат больше, чем в метке to_ts, значит разность, вычисленная функцией DATEDIFF(), была больше правильного значения на 1, тогда программный код вычитает из разности 1. Эта логическая схема, устраняющая неточность в вычислении DATEDIFF(), является ключевым элементом решения.
Почему не используют подобную логическую схему для вычисления разности временных элементов в значениях временных меток? Дело в том, что когда речь идет о временных элементах, разность двух временных меток может быть больше, чем может содержать целое число. В этом случае вычисление выйдет за пределы, и временные элементы придется обрабатывать отдельно.
Вычисление временных элементов
Теперь, когда правильно рассчитаны разности элементов даты, нужно возвратить из каждого элемента только часть, которую не покрывает элемент верхнего уровня. Например, даны временные метки 20030321 14:27:12.233 и 20060115 11:45:22.263. Вычисления в запросе в метке D Листинга 2 выдадут 2 года, 33 месяца и 1030 дней. При этом каждый элемент вычисляется независимо от других. Но чтобы показать результат в объединенных элементах, нужно возвратить только часть месяцев после вычитания элемента более высокого уровня, которая в этом случае составляет 9 месяцев (33 месяца минус 2 года). Подобным образом требуется возвратить только часть дней после вычитания лет и месяцев, которых в данном примере 24 дня (1030 дней минус 2 года и 9 месяцев). В итоге возвратится разница 2 года, 9 месяцев и 24 дня (плюс элементы нижних уровней). Следующие шаги возвращают элементы данных и подготавливают входные данные для расчета временных элементов.
Запрос в метке С принимает D3 в качестве своих входных данных и создает вторичную таблицу D4, которая показана в Таблице 6. Этот запрос просто перемещает метку from_ts вперед через каждую из трех разностей элементов дат (г, м, и д), соответственно создавая значения y_ts, m_ts и d_ts. Решение будет использовать каждую из этих временных меток в качестве указателя для возвращения только к нужной части элемента даты.
Запрос в метке В принимает D4 в качестве его входных данных и создает вторичную таблицу D5, которая показана в Таблице 7. Этот запрос возвращает только нужную часть каждого элемента даты следующим образом: он вычитает разность метки to_ts и указателя верхнего уровня из отдельного элемента даты. Запрос также вычисляет разницу в секундах (s) между указателем дня и to_ts. Листинг будет использовать s на следующем шаге для вычисления всех временных элементов за исключением миллисекунд.
Следующий запрос в метке А принимает D5 в качестве его входных данных и создает окончательный искомый результат. Он показан в Таблице 2. Запрос использует целочисленное деление (/) и по модулю (%) для вычисления элементов часов (h), минут (mi) и секунд (s), основанных на исходной разности значений секунд, полученной из вторичной таблицы D5. Запрос также вычисляет разницу в миллисекундах (ms) вычитая из 1000 элемент миллисекунд метки from_ts плюс элемент миллисекунд метки to_ts по модулю 1000 Число 1000 прибавляется к элементу ms, принадлежащему метке to_ts, а затем рассчитывается по модулю 1000. Это делается на тот случай, если элемент ms, принадлежащий to_ts, меньше, чем элемент ms, принадлежащий from_ts.
Выделение логики в Функцию
Итак, мы познакомились с методом правильного вычисления разницы между двумя датами. Приведенную логическую схему можно выделить в функцию, принимающую две даты в качестве входных данных. Теперь надо определить способ возвращения результата. Можно разработать функцию, основанную на значениях таблицы, и возвратить различные элементы дат и знак результата в разные столбцы результирующей строки. Либо можно разработать скалярную функцию, связывающую все выходные элементы в отдельную символьную строку. В Листинге 3 показан пример скалярной функции, вычисляющей разности дат.
Единственное изменение, которое нужно сделать в начальном решении, это добавить уровень, задающий формат вывода в качестве скалярного значения, как показано в метке А Листинга 3.
Для проверки функции выполните следующий программный код:
SELECT dbo.fn_datediff
('20030321
14:27:12.233',
'20060115
11:45:22.263');
В качестве выходных данных будет получено +0002-09-24 21:18:10.030.
Это значит, что разница между двумя введенными датами положительная,
2 года, 9 месяцев, 24 дня, 21 час, 18 минут. 10 секунд и 30 мс.
Дабы убедиться в правильности результата, используйте функцию DATEADD(),
чтобы суммировать все элементы с входными данными @from_ts.
Будут получены следующие входные данные @to_ts.
SELECT
DATEADD(ms, 30,
DATEADD(second, 10,
DATEADD(minute, 18, DATEADD(hour, 21,
DATEADD(day, 24,
DATEADD(month, 9,
DATEADD(year, 2,
'20030321
14:27:12.233')))))));
Как и множество проблем, затронутых мною в этом разделе, решение сложной задачи по определению разностей дат содержит большое количество логических схем.
Ицик Бен-Ган (itzik@solidqualitylearning.com) предподаватель в Solid Quality Learning. Читает лекции и консультирует на международном уровне. Является управляющим израильской группы пользователей SQL Server. MVP по SQL Server.
Листинг 1. Creating and Populating the TimeStamps Table
SET NOCOUNT ON;
USE tempdb;
GO
IF OBJECT_ID('dbo.TimeStamps', 'U') IS NOT NULL
DROP TABLE dbo.TimeStamps;
GO
CREATE TABLE dbo.TimeStamps
(
keycol INT NOT NULL PRIMARY KEY,
from_ts DATETIME NULL,
to_ts DATETIME NULL
);
INSERT INTO dbo.TimeStamps(keycol, from_ts, to_ts)
VALUES(1, '20040229 12:00:00.000', '20050228 13:00:00.000');
INSERT INTO dbo.TimeStamps(keycol, from_ts, to_ts)
VALUES(2, '20030321 14:27:12.233', '20060115 11:45:22.263');
INSERT INTO dbo.TimeStamps(keycol, from_ts, to_ts)
VALUES(3, '20060115 11:45:22.263', '20030321 14:27:12.233');
INSERT INTO dbo.TimeStamps(keycol, from_ts, to_ts)
VALUES(4, '20060212 00:00:00.000', '20060212 00:00:00.000');
INSERT INTO dbo.TimeStamps(keycol, from_ts, to_ts)
VALUES(5, '20051231 23:59:59.997', '20060101 00:00:00.000');
INSERT INTO dbo.TimeStamps(keycol, from_ts, to_ts)
VALUES(6, '17530101 00:00:00.000', '99991231 23:59:59.997');
GO
Листинг 2: Query That Calculates Correct Datetime Differences
BEGIN CALLOUT A
SELECT keycol, from_ts, to_ts, sgn, y, m, d,
s / 3600 AS h,
s % 3600 / 60 AS mi,
s % 60 AS s,
(1000 + DATEPART(ms, to_ts) - DATEPART(ms, from_ts)) % 1000 AS ms
FROM
END CALLOUT A
(
BEGIN CALLOUT B
SELECT keycol, from_ts, to_ts, sgn,
y,
m - DATEDIFF(month, from_ts, y_ts) AS m,
d - DATEDIFF(day, from_ts, m_ts) AS d,
DATEDIFF(second, d_ts, to_ts) AS s
FROM
END CALLOUT B
(
BEGIN CALLOUT C
SELECT *,
DATEADD(year, y, from_ts) AS y_ts,
DATEADD(month, m, from_ts) AS m_ts,
DATEADD(day, d, from_ts) AS d_ts
FROM
END CALLOUT C
(
BEGIN CALLOUT D
SELECT keycol, from_ts, to_ts, sgn,
y - CASE WHEN DATEADD(year, y, from_ts) > to_ts
THEN 1 ELSE 0 END AS y,
m - CASE WHEN DATEADD(month, m, from_ts) > to_ts
THEN 1 ELSE 0 END AS m,
d - CASE WHEN DATEADD(day, d, from_ts) > to_ts
THEN 1 ELSE 0 END AS d
FROM
END CALLOUT D
(
BEGIN CALLOUT E
SELECT *,
DATEDIFF(year, from_ts, to_ts) AS y,
DATEDIFF(month, from_ts, to_ts) AS m,
DATEDIFF(day, from_ts, to_ts) AS d
FROM
END CALLOUT E
(
BEGIN CALLOUT F
SELECT keycol,
CASE WHEN from_ts <= to_ts THEN from_ts ELSE to_ts END AS from_ts,
CASE WHEN from_ts <= to_ts THEN to_ts ELSE from_ts END AS to_ts,
CASE WHEN from_ts <= to_ts THEN 1 WHEN to_ts < from_ts THEN -1 END AS sgn
FROM dbo.TimeStamps
END CALLOUT F
) AS D1
) AS D2
) AS D3
) AS D4
) AS D5;
GO
Листинг 3. Function That Calculates Correct Datetime Differences
IF OBJECT_ID('dbo.fn_datediff', 'FN') IS NOT NULL
DROP FUNCTION dbo.fn_datediff;
GO
CREATE FUNCTION dbo.fn_datediff
(@from_ts AS DATETIME, @to_ts AS DATETIME) RETURNS VARCHAR(24)
AS
BEGIN
RETURN
(
BEGIN CALLOUT A
SELECT
CASE sgn WHEN 1 THEN '+' WHEN -1 THEN '-' END +
RIGHT('000' + CAST(y AS VARCHAR(4)), 4) + '-' +
RIGHT('0' + CAST(m AS VARCHAR(2)), 2) + '-' +
RIGHT('0' + CAST(d AS VARCHAR(2)), 2) + ' ' +
RIGHT('0' + CAST(h AS VARCHAR(2)), 2) + ':' +
RIGHT('0' + CAST(mi AS VARCHAR(2)), 2) + ':' +
RIGHT('0' + CAST(s AS VARCHAR(2)), 2) + '.' +
RIGHT('00' + CAST(ms AS VARCHAR(3)), 3)
FROM
END CALLOUT A
(
SELECT from_ts, to_ts, sgn, y, m, d,
s / 3600 AS h,
s % 3600 / 60 AS mi,
s % 60 AS s,
(1000 + DATEPART(ms, to_ts) - DATEPART(ms, from_ts)) % 1000 AS ms
FROM
(
SELECT from_ts, to_ts, sgn,
y,
m - DATEDIFF(month, from_ts, y_ts) AS m,
d - DATEDIFF(day, from_ts, m_ts) AS d,
DATEDIFF(second, d_ts, to_ts) AS s
FROM
(
SELECT *,
DATEADD(year, y, from_ts) AS y_ts,
DATEADD(month, m, from_ts) AS m_ts,
DATEDIFF(second, d_ts, to_ts) -
CASE
WHEN DATEPART(ms, to_ts) < DATEPART(ms, from_ts) THEN 1
ELSE 0
END AS s
FROM
(
SELECT from_ts, to_ts, sgn,
y - CASE WHEN DATEADD(year, y, from_ts) > to_ts
THEN 1 ELSE 0 END AS y,
m - CASE WHEN DATEADD(month, m, from_ts) > to_ts
THEN 1 ELSE 0 END AS m,
d - CASE WHEN DATEADD(day, d, from_ts) > to_ts
THEN 1 ELSE 0 END AS d
FROM
(
SELECT *,
DATEDIFF(year, from_ts, to_ts) AS y,
DATEDIFF(month, from_ts, to_ts) AS m,
DATEDIFF(day, from_ts, to_ts) AS d
FROM
(
SELECT
CASE WHEN from_ts <= to_ts THEN from_ts ELSE to_ts END AS from_ts,
CASE WHEN from_ts <= to_ts THEN to_ts ELSE from_ts END AS to_ts,
CASE WHEN from_ts <= to_ts THEN 1 WHEN to_ts < from_ts THEN -1 END AS sgn
FROM
(
SELECT @from_ts AS from_ts, @to_ts AS to_ts
) AS D0
) AS D1
) AS D2
) AS D3
) AS D4
) AS D5
) AS D6
)
END
GO
