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

Добавил Sergey Moiseenko on Суббота, 4 декабря. 2021

Пересказ статьи 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.

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

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

Обратные ссылки

URI этой записи для создания обратных ссылок (trackback)

Нет обратных ссылок

Добавить комментарий

Имя

Домашняя страница

Комментарий

В ответ на

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

Enter the string from the spam-prevention image above:

Form options

Запомнить информацию?

Добавленные комментарии должны будут пройти модерацию прежде, чем будут показаны.

SQL-Ex blog