Skip to content

Получите удовольствие от арифметики с DATETIME

Пересказ статьи Eitan Blumin. Fun with DATETIME Arithmetics


Нулевое значение


Тип данных datetime имеет "нулевое значение", которое представляется как 1900-01-01 00:00:00.

Оно может быть представлено литеральным значением 0. Проверим:


SELECT CONVERT(datetime, 0)

Это дает 1900-01-01 00:00:00

Вы можете думать о типе данных datetime как о числе дней, прошедших от 1900-01-01.

Это также может быть десятичным числом, в том числе отрицательным:

SELECT
CONVERT(datetime, 1.5)
, CONVERT(datetime, -3.5)

Результатом будет 1900-01-02 12:00:00.000 и 1899-12-28 12:00:00.000 соответственно.

Что с DATEADD?


Проверьте фрагмент кода ниже:

DECLARE @d1 DATETIME, @d2 DATETIME
SET @d1 = 0
SELECT @d1 -- результат: 1900-01-01 00:00:00.000
SET @d1 = DATEADD(day, 1, @d1)
SELECT @d1 -- результат: 1900-01-02 00:00:00.000
SET @d1 = DATEADD(hour, 3, @d1)
SELECT @d1 -- результат: 1900-01-02 03:00:00.000
SET @d1 = DATEADD(minute, 35, DATEADD(second, 15, @d1))
SELECT @d1 -- результат: 1900-01-02 03:35:15.000

Начиная с "нулевого" значения datetime, мы смогли постепенно "добавлять" к нему компоненты даты до тех пор, пока не получили сложное значение некоторого вида.

Мы также можем добавить литералы времени и даты/времени подобные следующим:

SET @d1 += '10:30.5'
SELECT @d1 -- результат: 1900-01-02 14:05:15.500
SET @d1 += '1900-01-01 2:10.4'
SELECT @d1 -- результат: 1900-01-02 16:15:15.900

Математические сложение и вычитание можно выполнять между двумя типами данных datetime:

SET @d2 = '1900-03-30 18:00'
SELECT
@d1 + @d2 -- результат: 1900-04-01 10:15:15.900
, @d1 - @d2 -- результат: 1899-10-05 22:15:15.900
, @d2 - @d1 -- результат: 1900-03-29 01:44:44.100

Это означает, что мы можем иметь базовую арифметику datetime в SQL Server. Мы можем использовать вычитание, чтобы найти точную разность между двумя датами, и использовать сложение для добавления точного интервала к столбцу или переменной типа datetime.

Что насчет datetime2?


Важно отметить, что тип данных datetime2 не поддерживает ту же самую функциональность.

Если попытаться выполнить арифметические действия с ним, вы должны получить ошибки, подобные следующим:

Msg 8117, Level 16, State 1, Line 12
Operand data type datetime2 is invalid for add operator.
Msg 402, Level 16, State 1, Line 12
The data types datetime2 and datetime are incompatible in the add operator.

Поэтому, если у вас есть некоторые данные типа datetime2, вам придется преобразовать их к datetime, прежде чем выполнять то, что вы здесь видите.

Есть ли планы по умножению и делению дат?


Умножение (*) и деление (/) не будут работать:

SET @d2 = @d1 * 2.0

Msg 257, Level 16, State 3, Line 21
Implicit conversion from data type datetime to numeric is not allowed. Use the CONVERT function to run this query.

Но это возможно, если преобразовать значения datetime сначала во float (хотя я не совсем уверен, чтобы вам когда-нибудь что-то подобное понадобилось):

SET @d2 = CONVERT(float, @d1) * 2.0
SELECT @d2 -- результат: 1900-01-04 08:30:31.800
SELECT CONVERT(datetime, CONVERT(float, @d1) * CONVERT(float, @d2))
-- результат: 1900-01-06 15:02:05.420

Когда что-то пойдет не так?


Давайте попробуем поиграть немного с кварталами:

SELECT
DATEADD(quarter, 1, 0), -- результат: 1900-04-01 00:00:00.000
DATEADD(quarter, 2, 0), -- результат: 1900-07-01 00:00:00.000
DATEADD(quarter, 3, 0) -- результат: 1900-10-01 00:00:00.000

Пока выглядит правдоподобно. Каждый квартал становится эквивалентным 3-м месяцам, как и ожидалось.

Но если мы немного усложним задачу, например добавив квартал к существующему значению datetime, а затем использовав вычитание, чтобы посмотреть, как будет выглядеть разность datetime?

DECLARE @dt datetime = '2021-03-25'
SELECT DATEADD(quarter, 1, @dt) - @dt
-- результат: 1900-04-03 00:00:00.000

Стойте, это выглядит неправильно... Почему вдруг компонента дня равна "3"? Разве она не должна предположительно оставаться "1"? Я только хотел добавить несколько месяцев... Почему это повлияло на день?

Давайте посмотрим по частям:

SELECT DATEADD(quarter, 1, @dt), @dt

Результат 2021-06-25 и 2021-03-25.

Это странно... Выглядит просто как разница в 3 месяца, как и ожидалось... Тогда откуда взялись эти 2 лишних дня?

Давайте попробуем разбить еще дальше:

SELECT
DATEADD(month, 3, 0), -- результат: 1900-04-01 00:00:00.000
@dt + '1900-04-01', -- давайте попробуем добавить 3 месяца. результат: 2021-06-23
DATEADD(month, 3, @dt) -- будет ли это то же самое, что и DATEADD? результат: 2021-06-25

Интересно. Когда мы пытаемся добавить 3 месяца с помощью арифметического метода вместо DATEADD, мы получаем значение datetime 2021-06-23, которое действительно теряет пару дней в сравнении с ожидаемым 2021-06-25.

Это происходит потому, что месяцы могут содержать 28, 29, 30 или 31 день, и, следовательно, иметь "несогласованное" число дней в них. Один месяц не всегда эквивалентен другому.

Вывод: арифметика datetime может быть совместима с использованием DATEDIFF/DATEADD, но только пока вы используете части даты, которые имеют "согласованный размер". Месяцы и кварталы, следовательно, не будут работать, как хотелось бы. Годы могут быть также проблематичными по причине високосных годов, в которых 366 дней, а не 365.

Можете ли вы сделать это красивее?


Как вы, вероятно, заметили, интервал datetime выглядит не очень хорошо.

Это 1900-что-то_еще может вызывать трудности при чтении, особенно когда интервал составляет более нескольких дней.

Однако с помощью нескольких трюков с функциями CONVERT и DATEDIFF мы можем подготовить для себя удобную скалярную функцию, которая преобразует что-то типа:

1900-03-05 11:22:33

в что-то такое:

63d,11:22:33:000

Функция, подобная приведенной ниже, должна справиться с этой задачей:

CREATE OR ALTER FUNCTION dbo.FormatInterval (@dt DATETIME)
RETURNS VARCHAR(100)
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS
BEGIN
RETURN
ISNULL(NULLIF(CONVERT(varchar(100), DATEDIFF(dd,0, @dt)), 0) + 'd,', '')
+ CONVERT(varchar(100), @dt, 114)
END

Так для чего это вообще нужно?


Мы можем использовать арифметику datetime в качестве универсальной и надежной альтернативы DATEADD.

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

DECLARE
@FromDate DATETIME = CONVERT(DATE, GETDATE()-1),
@EndDate DATETIME = GETDATE(),
@PeriodType CHAR(2) = 'H'
/*
Поддерживаемые типы периодов:
MI - Minute
H - Hour
D - Day
W - Week
M - Month
Q - Quarter
T - Trimester
HY - Half-Year
Y - Year
*/
;
WITH Periods
AS
(
SELECT
PeriodNum = 1,
StartDate = @FromDate,
EndDate =
CASE @PeriodType
WHEN 'MI' THEN
DATEADD(minute,1,@FromDate)
WHEN 'H' THEN
DATEADD(hh,1,@FromDate)
WHEN 'D' THEN
DATEADD(dd,1,@FromDate)
WHEN 'W' THEN
DATEADD(ww,1,@FromDate)
WHEN 'M' THEN
DATEADD(mm,1,@FromDate)
WHEN 'Q' THEN
DATEADD(Q,1,@FromDate)
WHEN 'T' THEN
DATEADD(mm,4,@FromDate)
WHEN 'HY' THEN
DATEADD(mm,6,@FromDate)
WHEN 'Y' THEN
DATEADD(yyyy,1,@FromDate)
END
UNION ALL
SELECT
PeriodNum = PeriodNum + 1,
StartDate = EndDate,
EndDate =
CASE @PeriodType
WHEN 'MI' THEN
DATEADD(minute,1,EndDate)
WHEN 'H' THEN
DATEADD(hh,1,EndDate)
WHEN 'D' THEN
DATEADD(dd,1,EndDate)
WHEN 'W' THEN
DATEADD(ww,1,EndDate)
WHEN 'M' THEN
DATEADD(mm,1,EndDate)
WHEN 'Q' THEN
DATEADD(Q,1,EndDate)
WHEN 'T' THEN
DATEADD(mm,4,EndDate)
WHEN 'HY' THEN
DATEADD(mm,6,EndDate)
WHEN 'Y' THEN
DATEADD(yyyy,1,EndDate)
END
FROM
Periods
WHERE
EndDate < @EndDate
)
SELECT PeriodNum, StartDate, EndDate
FROM Periods
OPTION (MAXRECURSION 0);

Но при использовании арифметики DATETIME мы можем более гибко задавать интервалы и даже упростить наш код. Например, мы можем сгенерировать 10-минутные интервалы таким образом:

DECLARE
@FromDate DATETIME = CONVERT(DATE, GETDATE()-1),
@EndDate DATETIME = GETDATE(),
@Interval DATETIME = '00:10:00'
;
WITH Periods
AS
(
SELECT
PeriodNum = 1,
StartDate = @FromDate,
EndDate = @FromDate + @Interval
UNION ALL
SELECT
PeriodNum = PeriodNum + 1,
StartDate = EndDate,
EndDate = EndDate + @Interval
FROM
Periods
WHERE
EndDate < @EndDate - @Interval
)
SELECT PeriodNum, StartDate, EndDate
FROM Periods
OPTION (MAXRECURSION 0);

На вид это много проще, не так ли?

Однако, как упоминалось ранее, мы не смогли бы надежно использовать зависящие от месяцев интервалы, такие как месяцы, кварталы, триместры или годы. Так что здесь действительно есть своего рода компромисс. Хотя… Можно было бы придумать способ написать здесь сверхнадежный код, который мог бы использовать оба мира, используя некоторую магию CASE WHEN (даже если это не уменьшит число строк кода).

Мы можем также использовать арифметику datetime как альтернативу DATEDIFF.

Например, мы можем использовать её для отображения продолжительности команды в легком для понимания представлении:

SELECT
session_id, start_time, command
, duration =
ISNULL(NULLIF(CONVERT(varchar(100), DATEDIFF(dd,0, GETDATE() - start_time)), 0) + 'd,', '')
+ CONVERT(varchar(100), GETDATE() - start_time, 114)
FROM sys.dm_exec_requests


Пример вывода с моего удивительно бездействующего ноутбука

Зачем соглашаться на секунды или миллисекунды, я прав?

Заключение


Знакомство с сильными сторонами арифметики datetime может потенциально избавить нас от головной боли, связанной с ограничениями функций DATEADD и DATEDIFF.

Это может помочь нам с написанием более чистого и понятного кода, сделав при этом нас даже более продуктивными.

Я лично начал использовать эти приемы во всех возможных случаях, и мне очень нравится моя новая привычка.

Trackbacks

No Trackbacks

Comments

Display comments as Linear | Threaded

No comments

The author does not allow comments to this entry

Add Comment

Enclosing asterisks marks text as bold (*word*), underscore are made via _word_.
Standard emoticons like :-) and ;-) are converted to images.

To prevent automated Bots from commentspamming, please enter the string you see in the image below in the appropriate input box. Your comment will only be submitted if the strings match. Please ensure that your browser supports and accepts cookies, or your comment cannot be verified correctly.
CAPTCHA

Form options

Submitted comments will be subject to moderation before being displayed.