Конференція Highload fwdays'19 — Autoscale, MySQL 8.0, Neo4j, Kafka and AWS Lambda | 05.10 | Київ

Microsoft SQL Server :: DATETIME inside

Здравствуйте друзья, коллеги.

Проблема

Прошу всех заинтересованных проделать эксперимент.

Для этого выполниете следующий 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′

Вас ничего не смущает ?

  1. Ничего, что в BETWEEN из case 1 нижняя граница больше верхней ? (’2015-01-01 12:30:00.004′ > ’2015-01-01 12:30:00.002′) и значение поля [Date] не попадает в этот диапазон
  2. Ничего, что в case 2 значение поля [Date] (’2015-01-01 12:30:00.003′) не попадает в диапазон [’2015-01-01 12:30:00.002′, ’2015-01-01 12:30:00.002′], но тем не менее запись попадает в результирующий набор ?
  3. Ничего, что в 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, 001000
002, 003, 004003
005, 006, 007, 008007
009000 (и +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

Ссылки

Благодарю за внимание и хорошей, счастливой Вам жизни.

До новых встреч.

LinkedIn
Допустимые теги: blockquote, a, pre, code, ul, ol, li, b, i, del.
Ctrl + Enter
Допустимые теги: blockquote, a, pre, code, ul, ol, li, b, i, del.
Ctrl + Enter

MSSQL Server always stores datatime data type for discrete of 0.003 second.
That’s why you never get from the function of GETDATE() values of ’2015-12-02 00:00:00.001′, ’2015-12-02 00:00:00.002′, ’2015-12-02 00:00:00.004′, etc.
But if you add these values manually, you will have a your result.
You should use GETDATE() function to get a correct result as datetime data type with correct numbers at the end.
or you can use type conversion, eg.
SELECT CAST(’2015-02-05 00:00:00.012′ AS datetime)

You should use GETDATE() function to get a correct result as datetime data type with correct numbers at the end.
Я бы рекомендовал использовать, начиная с Microsoft SQL Server ® 2008:
вместо GETDATE() -> SYSDATETIME(),
вместо GETUTCDATE() -> SYSUTCDATETIME().
Дело в том, что функции SYSDATETIME() и SYSUTCDATETIME() возвращают значение типа DATETIME2 вместо DATETIME (GETDATE(), GETUTCDATE()).
SELECT CAST(’2015-02-05 00:00:00.012′ AS datetime)
Вы ошиблись, правильно будет:
SELECT CAST(’2015-02-05 00:00:00.012′ AS DATETIME2(3))

Первоисточник.

It depends what do you want to get.
If you use incorrect values for datetime type, you should use type conversion to get correct values.
You should know your data and data types MSSQL used to get correct result.

Update
Не рекомендуется использовать DATETIME начиная с Microsoft SQL Server 2008 ®. Вмест этого использовать DATETIME2(3). Как результат получаем большую точность при меньшем объеме хранения (100 ns per 7 bytes vs 000, 003, 007 ms per 8 bytes)

а нафига вам там тысячные ?

Не ну в данном случае тысячные были не принципиальны, но сам факт.
Да и на будущее урок, ведь бывают такие задачи, когда это принципиально.
Вот например задача перегнать большой объём исторических данных из MongoDB в Microsoft SQL Server ®. Причём в одной из таблиц нет Primary Key. Поэтому надо использовть составной ключ, в состав которого входит поле типа DATETIME. Так вот, что бы сохранить корелляцию между проимортированными и исходными данными — нужно импортировать дату и время один в один, как в оригинале, без всяких округлений. А то связь будет утрачена. Это кстати пример из реальной жизни.

понятно, обычно есть еще уникальный номер транзакции

И как этим воспользоваться ? Как нормер транзакции корелирует с внешней базой ? Или вы предлогаете на каждый инсерт класть в исходную и целевую базу TransactionId вставленной записи ? А если записей очень много, не сильно ли жирно будет открывать/закрывать отдельную транзакцию для каждой записи ?

но вы вроде ответили что у вас кий составной и дей.тайм является частью этого ключа.

понятно, обычно есть еще уникальный номер транзакции
Я про то, как Вы предлогаете воспользоваться уникальным номером транзакции ?

имелось в виду что не нужно хранить время до тысячных если есть ID , но это не ваш случай.

наверное, долго пришлось повозиться, чтоб понять — какого хрена?

Та не — быстро, вопрос 5ти — 10ти минут. Коллега прочёл на MSDN спецификацию типа DATETIME и мы сразу врубилсь, в чём дело. Потом пришлось немного поморочаться, что бы понять когда он к 000, 003, 007 округляет, сделать пару экспериментов, что бы убедиться что дело именно в этом. И с чистой совестью закрыть этот вопрос. Но было неожиданно — делаешь запрос и в него явно попадают записи, которых не должно быть ?!?! )

Для этого выполниете следующий Microsoft SQL Server ® хранит дату в формате с большей чем 0.001 точностью, но при выводе на экран делает TRUNC, а в запросах использует ROUND.
тег неверно закрыли, часть сообщения скрыта.

Подписаться на комментарии