Обчислення різниці між показниками дати і часу (исходники), Різне, Програмування, статті

Програмісти, що працюють з 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”)))))));

Як і безліч проблем, порушених мною в цьому розділі, вирішення складної задачі по визначенню різниць дат містить велику кількість логічних схем.


Іцик Бен-Ган – викладачем в 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

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*