Microsoft SQL Server :: DATETIME inside
Підписуйтеся на Telegram-канал «DOU #tech», щоб не пропустити нові технічні статті
Здравствуйте друзья, коллеги.
Проблема
Прошу всех заинтересованных проделать эксперимент.Для этого выполниете следующий T-SQL скрипт:
DECLARE @table TABLE ( [Date] DATETIME ) INSERT INTO @table ([Date]) VALUES('2015-01-01 12:30:00.003') -- Case 1. Wrong range. SELECT * FROM @table WHERE [Date] BETWEEN '2015-01-01 12:30:00.004' AND '2015-01-01 12:30:00.002' -- Result: '2015-01-01 12:30:00.003'. -- Case 2. Start & end dates in the between predicate are less than the record [Date] field value. SELECT * FROM @table WHERE [Date] BETWEEN '2015-01-01 12:30:00.002' AND '2015-01-01 12:30:00.002' -- Result: Still '2015-01-01 12:30:00.003'. -- Case 3. Start & end dates in the between predicate are greater than the record [Date] field value. SELECT * FROM @table WHERE [Date] BETWEEN '2015-01-01 12:30:00.004' AND '2015-01-01 12:30:00.004' -- Result: Still '2015-01-01 12:30:00.003'.
Во всех кейсах у Вас должна вернуться одна запись, в поле [Date] которой будет значение: ’2015-01-01 12:30:00.003′
Вас ничего не смущает ?
- Ничего, что в BETWEEN из case 1 нижняя граница больше верхней ? (’2015-01-01 12:30:00.004′ > ’2015-01-01 12:30:00.002′) и значение поля [Date] не попадает в этот диапазон
- Ничего, что в case 2 значение поля [Date] (’2015-01-01 12:30:00.003′) не попадает в диапазон [’2015-01-01 12:30:00.002′, ’2015-01-01 12:30:00.002′], но тем не менее запись попадает в результирующий набор ?
- Ничего, что в case 3 значение поля [Date] (’2015-01-01 12:30:00.003′) не попадает в диапазон [’2015-01-01 12:30:00.004′, ’2015-01-01 12:30:00.004′], но тем не менее запись попадает в результирующий набор ?
Меня вначале это смутило, однако интуиция подсказывала, что где-то не заднем плане происходят некие округления, потому как это работало только в диапазлне +/- 1 миллисекунды. Если выполнить этот скрипт:
DECLARE @table TABLE ( [Date] DATETIME ) INSERT INTO @table ([Date]) VALUES('2015-01-01 12:30:00.003') -- Start and End dates are less then the [Date] value for the 2 milliseconds. SELECT * FROM @table WHERE [Date] BETWEEN '2015-01-01 12:30:00.001' AND '2015-01-01 12:30:00.001' --Result: Nothing., то запись не попадёт в выборку.
Вначале мы подумали, что тут дело в том, что на самом деле Microsoft SQL Server ® хранит дату в формате с большей чем 0.001 точностью, но при выводе на экран делает TRUNC, а в запросах использует ROUND.
Однако один из моих коллег дал мне эту ссылку.
Где чёрным по белому написано: Accuracy. Rounded to increments of 000, .003, or .007 seconds.
Разбор в деталях
Так что же на самом деле получается ?При вычислении границ предиката BETWEEN происходит округление дат. Соответственно ’2015-01-01 12:30:00.002′ округляется до ’2015-01-01 12:30:00.003′ и ’2015-01-01 12:30:00.004′ тоже до ’2015-01-01 12:30:00.003′ (см. Таблица округлений).
Таблица округлений
<tbody></tbody><tbody></tbody><tbody></tbody><tbody></tbody><tbody></tbody>
Исходное значение | Округлённое |
000, 001 | 000 |
002, 003, 004 | 003 |
005, 006, 007, 008 | 007 |
009 | 000 (и +1) в следующем разряде |
Соответственно во всех трёх случаях получаем BETWEEN ’2015-01-01 12:30:00.003′ AND ’2015-01-01 12:30:00.003′. И как результат, наша запись удовлетвояет этому предикату и попадает в результирующий набор.
Как Вам а ? :)
Решение
Использовать в предикате (Microsoft SQL Server 2008 ® +) тип данных для представления даты и времени: DATETIME2. А именно DATETIME2(3). Точность данного типа данных — 100 наносекунд.Проведём последний эксперимент:
DECLARE @table TABLE ( [Date] DATETIME ) DECLARE @startDate DATETIME2(3), @endDate DATETIME2(3) INSERT INTO @table ([Date]) VALUES('2015-01-01 12:30:00.003') -- Case 1. Wrong range. SELECT @startDate = '2015-01-01 12:30:00.004', @endDate = '2015-01-01 12:30:00.002' SELECT * FROM @table WHERE [Date] BETWEEN @startDate AND @endDate -- Result: Nothing -- Case 2. Start & end dates in the between predicate are less than the record [Date] field value. SELECT @startDate = '2015-01-01 12:30:00.002', @endDate = '2015-01-01 12:30:00.002' SELECT * FROM @table WHERE [Date] BETWEEN @startDate AND @endDate -- Result: Nothing -- Case 3. Start & end dates in the between predicate are greater than the record [Date] field value. SELECT @startDate = '2015-01-01 12:30:00.004', @endDate = '2015-01-01 12:30:00.004' SELECT * FROM @table WHERE [Date] BETWEEN @startDate AND @endDate -- Result: Nothing
Во всех случаях, запись не попала в результирующий набор, что очень логично и предсказуемо.
Резюме
Так в когда же использовать обычный DATETIME, а в каких DATETIME2 ?Я бы порекомендовал следующие критерии:
В таблицах:
Не важна высокая точность хранения дат. Скажем бухгалтерский учёт, где важна дата и время с точностью до секунд — используем DATETIME.- Вообще не рекомендуется использовать DATETIME начиная с Microsoft SQL Server 2008 ®. Вмест этого использовать DATETIME2(3). Как результат получаем большую точность при меньшем объеме хранения (100 ns per 7 bytes vs 000, 003, 007 ms per 8 bytes)
- Нужна высокая точность хранения дат (например вы не хотите потерять корелляцию по дате, проимпортированных данных и данных в источнике) — DATETIME2
В запросах:
Выборки, где миллисикунды не играют роли — DATETIMEВыборки, для которых попадание(выпадание) нескольких записей не влечёт никаких последствий (выборки в логах, и т.д.) — DATETIME- Вообще не рекомендуется использовать DATETIME начиная с Microsoft SQL Server 2008 ®. Вмест этого использовать DATETIME2(3). Как результат получаем большую точность при меньшем объеме хранения (100 ns per 7 bytes vs 000, 003, 007 ms per 8 bytes)
- Выборки по real-time данным с гарантированным попадание в выборку, только записей строго удовлетвояющих условию — DATETIME2
Ссылки
Благодарю за внимание и хорошей, счастливой Вам жизни.
До новых встреч.
15 коментарів
Додати коментар Підписатись на коментаріВідписатись від коментарів