DateTime2 vs DateTime в SQL Server

Какой из них:

- рекомендуемый способ хранения даты и времени в SQL Server 2008 +?

Я знаю различия в точности (и, возможно, место для хранения), но игнорируя их на данный момент, есть ли лучший практический документ о том, когда использовать что, или, может быть, мы должны использовать только datetime2?

+684
источник поделиться
14 ответов

В документации MSDN для datetime рекомендуется использовать datetime2. Вот их рекомендация:

Используйте типы данных time, date, datetime2 и datetimeoffset для новой работы. Эти типы соответствуют стандарту SQL. Они более портативны. time, datetime2 и datetimeoffset обеспечивают большую точность секунд. datetimeoffset обеспечивает поддержку часовых поясов для глобально развернутых приложений.

datetime2 имеет больший диапазон дат, большую дробную точность по умолчанию и необязательную точность, указанную пользователем. Также в зависимости от заданной пользователем точности он может использовать меньше памяти.

+568
источник

DATETIME2 имеет диапазон дат от "0001/01/01" до "9999/12/31", в то время как тип DATETIME поддерживает только год 1753-9999.

Кроме того, если вам нужно, DATETIME2 может быть более точным с точки зрения времени; DATETIME ограничено 3 1/3 миллисекундами, а DATETIME2 может быть точным до 100 нс.

Оба типа отображают System.DateTime в .NET - нет никакой разницы.

Если у вас есть выбор, я бы рекомендовал использовать DATETIME2, когда это возможно. Я не вижу никаких преимуществ при использовании DATETIME (за исключением обратной совместимости) - у вас будет меньше проблем (с датами, выходящими за пределы диапазона, и такими неприятностями).

Плюс: если вам нужна только дата (без временной части), используйте DATE - она ​​так же хороша, как DATETIME2, и экономит ваше пространство!:-) То же самое касается только времени - используйте TIME. Для чего нужны эти типы!

+461
источник
другие ответы

Связанные вопросы


Похожие вопросы

datetime2 выигрывает в большинстве аспектов, кроме (совместимость старых приложений)

  • больший диапазон значений
  • лучше Точность
  • меньше место для хранения (если указана дополнительная точность, заданная пользователем)

SQL Date and time data types compare - datetime,datetime2,date,TIME

обратите внимание на следующие пункты

  • Синтаксис
    • datetime2 [(точность дробных секунд = > Посмотрите ниже размера хранилища)]
  • Точность, масштаб
    • от 0 до 7 цифр с точностью 100 нс.
    • Точность по умолчанию - 7 цифр.
  • Размер хранилища
    • 6 байт для точности менее 3;
    • 7 байт для точности 3 и 4.
    • Для любой другой точности требуется 8 байт.
  • DateTime2 (3) имеют такое же количество цифр, что и DateTime, но используют 7 байтов памяти вместо 8 байтов (SQLHINTS- DateTime Vs DateTime2)
  • Подробнее о datetime2 (статья MSDN Transact-SQL)

источник изображения: MCTS Self-Paced Training Kit (Экзамен 70-432): Microsoft® SQL Server® 2008 - Внедрение и обслуживание Глава 3: Таблицы → Урок 1: Создание таблиц → стр. 66

+183
источник

Я соглашаюсь с @marc_s и @Adam_Poward - метод DateTime2 является предпочтительным методом продвижения вперед. Он имеет более широкий диапазон дат, более высокую точность и использует равное или меньшее количество хранения (в зависимости от точности).

Одна вещь, которую обсудили, однако...
Состояние @Marc_s: Both types map to System.DateTime in .NET - no difference there. Это верно, , однако инверсия не является истиной... и имеет значение при проведении поиска диапазона дат (например, "найдите все записи, измененные в 5/5/2010" ).

.NET версия Datetime имеет аналогичный диапазон и точность до DateTime2. При сопоставлении .net Datetime вплоть до старого SQL Datetime происходит неявное округление. Старый SQL Datetime с точностью до 3 миллисекунд. Это означает, что 11:59:59.997 находится как можно ближе к концу дня. Все, что выше, округляется до следующего дня.

Попробуйте следующее:

declare @d1 datetime   = '5/5/2010 23:59:59.999'
declare @d2 datetime2  = '5/5/2010 23:59:59.999'
declare @d3 datetime   = '5/5/2010 23:59:59.997'
select @d1 as 'IAmMay6BecauseOfRounding', @d2 'May5', @d3 'StillMay5Because2msEarlier'

Избегание этого неявного округления является существенной причиной для перехода на DateTime2. Неявное округление дат явно вызывает путаницу:

+103
источник

DateTime2 вызывает хаос, если вы являетесь разработчиком Access, который пытается написать Now() в поле. Просто выполнил миграцию Access → SQL 2008 R2 и поместил все поля datetime в DateTime2. Добавление записи с помощью Now() по мере того, как значение бомбардируется. Это было нормально 1 января 2012 года 2:53:04, но не 10/10/2012 2:53:04 PM.

Как только персонаж сделал разницу. Надеюсь, это поможет кому-то.

+15
источник

Вот пример, который покажет вам различия в размере (байтах) хранения и точности между smalldatetime, datetime, datetime2 (0) и datetime2 (7):

DECLARE @temp TABLE (
    sdt smalldatetime,
    dt datetime,
    dt20 datetime2(0),
    dt27 datetime2(7)
)

INSERT @temp
SELECT getdate(),getdate(),getdate(),getdate()

SELECT sdt,DATALENGTH(sdt) as sdt_bytes,
    dt,DATALENGTH(dt) as dt_bytes,
    dt20,DATALENGTH(dt20) as dt20_bytes,
    dt27, DATALENGTH(dt27) as dt27_bytes FROM @temp

который возвращает

sdt                  sdt_bytes  dt                       dt_bytes  dt20                 dt20_bytes  dt27                         dt27_bytes
2015-09-11 11:26:00  4          2015-09-11 11:25:42.417  8         2015-09-11 11:25:42  6           2015-09-11 11:25:42.4170000  8

Итак, если я хочу хранить информацию до второго, но не до миллисекунды, я могу сохранить 2 байта каждый, если я использую datetime2 (0) вместо datetime или datetime2 (7).

+14
источник

Почти все ответы и комментарии были тяжелыми для плюсов и легкими минусами. Вот краткий обзор всех плюсов и минусов на данный момент плюс некоторые важные минусы (в # 2 ниже), которые я видел, упоминал только один раз или не упомянул вообще.

  1. ПЛЮСЫ:

1.1. Более совместимый с ISO (ISO 8601) (хотя я не знаю, как это проявляется на практике).

1.2. Большой диапазон (с 1/1/0001 по 12/31/9999 по сравнению с 1/1/1753-12/31/9999) (хотя дополнительный диапазон, все до 1753 года, скорее всего, не будет использоваться, кроме как, в исторических, астрономических, геологических и др. приложениях).

1.3. Точно соответствует диапазону .NET Типы DateTime Types (хотя оба преобразуются туда и обратно без специального кодирования, если значения находятся в пределах диапазона целевых типов и точности, за исключением Con # 2.1 ниже, иначе произойдет ошибка/округление).

1.4. Более высокая точность (100 наносекунд, то есть 0,000,000,1 секунды против 3,33 миллисекунды, то есть 0,003,33 секунды) (хотя дополнительная точность, скорее всего, не будет использоваться, за исключением, например, в инженерных/научных приложениях).

1,5. При настройке на аналогичную (как в 1 миллисекунде не "ту же" (как в 3.33 миллисекундах), как утверждал Иман Абиди) точность как DateTime, использует меньше места (7 против 8 байтов), но тогда, конечно, вы теряете точность выгода, которая, вероятно, является одной из двух (другая - диапазон), которую чаще всего рекламируют, хотя, вероятно, это излишние выгоды).

  1. МИНУСЫ:

2.1. При передаче параметра в .NET SqlCommand вы должны указать System.Data.SqlDbType.DateTime2 если вы можете передавать значение вне диапазона DateTime и/или точности SQL Server, поскольку по умолчанию используется значение System.Data.SqlDbType.DateTime.

2.2. Невозможно неявно/легко преобразовать в числовое значение с плавающей точкой (количество дней с минимальной даты и времени), чтобы выполнить следующие действия с/в нем в выражениях SQL Server с использованием числовых значений и операторов:

2.2.1. добавить или вычесть # дней или неполных дней. Примечание. Использование функции DateAdd в качестве обходного пути не является тривиальным, когда вам нужно учитывать несколько, если не все части даты-времени.

2.2.2. возьмите разницу между двумя датами для расчета "возраста". Примечание. DateDiff этого нельзя просто использовать DateDiff SQL-серверов, поскольку она не вычисляет age поскольку большинство людей ожидают, что в случае, если два значения даты-времени пересекают границу даты и времени календаря/часов в единицах, указанных даже для крошечная доля этой единицы, она будет возвращать разницу как 1 этой единицы против 0. Например, DateDiff в Day двух дат-раз с DateDiff всего в 1 миллисекунду вернет 1 против 0 (дней), если эти даты-времена в разные календарные дни (т.е. "1999-12-31 23: 59: 59.9999999" и "2000-01-01 00: 00: 00.0000000"). Те же самые значения даты-времени с разницей в 1 миллисекунду, если они перемещаются таким образом, чтобы они не пересекали календарный день, возвращают "DateDiff" в Day 0 (дней).

2.2.3. возьмите Avg даты-времени (в совокупном запросе), просто преобразовав сначала "Float", а затем снова обратно в DateTime.

ПРИМЕЧАНИЕ. Чтобы преобразовать DateTime2 в числовое значение, вы должны выполнить что-то вроде следующей формулы, которая по-прежнему предполагает, что ваши значения не меньше 1970 года (что означает, что вы теряете весь дополнительный диапазон плюс еще 217 лет. Примечание. быть в состоянии просто настроить формулу, чтобы учесть дополнительный диапазон, потому что вы можете столкнуться с проблемами переполнения чисел.

25567 + (DATEDIFF(SECOND, {d '1970-01-01'}, @Time) + DATEPART(nanosecond, @Time)/1.0E + 9)/86400.0 - Источник: " https://siderite.blogspot.com/2015/08/how-to-translate-t-sql-datetime2-to.html "

Конечно, вы можете также Cast для DateTime первого (и при необходимости снова к DateTime2), но вы потеряете точность и диапазон (все до года 1753) преимущества DateTime2 против DateTime, которые являются Prolly 2 самым большим, а также в то же время, как минимум, требуется 2 наименее вероятных, поэтому возникает вопрос, зачем его использовать, когда вы теряете неявные/простые преобразования в числовые с плавающей точкой (количество дней) для сложения/вычитания/"возраста" (против DateDiff)/Avg выгода calcs, которая является большой в моем опыте.

Между прочим, Avg даты-времени является (или, по крайней мере, должно быть) важным вариантом использования. а) Помимо использования в получении средней продолжительности, когда дата-время (поскольку общая базовая дата-время) используются для представления длительности (обычная практика), б) также полезно получить статистику типа панели мониторинга о том, какая средняя дата- время находится в столбце даты-времени диапазона/группы строк. c) Стандартный (или, по крайней мере, должен быть стандартным) специальный запрос для мониторинга/устранения неисправностей значений в столбце, которые могут быть недействительными никогда/больше и/или, возможно, должны быть признаны устаревшими, заключается в перечислении для каждого значения счетчика вхождений и (если доступно) метки даты и времени Min, Avg и Max связанные с этим значением.

+13
источник

при увеличении точности с datetime2 некоторые клиенты не поддерживают дату, время или дату-время2 и заставить вас преобразовать в строковый литерал. В частности, Microsoft упоминает проблемы ODBC, OLE DB, JDBC и SqlClient "нижнего уровня" с этими типами данных и имеет диаграмму показывающую, как каждый может отображать тип.

Если значение совместимости превышает точность, используйте datetime

+9
источник

Интерпретация строк даты в datetime и datetime2 также может быть различной при использовании настроек, отличных от US DATEFORMAT. Например.

set dateformat dmy
declare @d datetime, @d2 datetime2
select @d = '2013-06-05', @d2 = '2013-06-05'
select @d, @d2

Это возвращает 2013-05-06 (т.е. 6 мая) для datetime и 2013-06-05 (т.е. 5 июня) для datetime2. Однако, если DATEFORMAT установлен на mdy, оба @d и @d2 возвращают 2013-06-05.

Поведение datetime, по-видимому, противоречит документации MSDN SET DATEFORMAT, которая гласит: некоторые форматы символьных строк, например ISO 8601, интерпретируются независимо от настройки DATEFORMAT. Очевидно, это не так!

До тех пор, пока я не был укушен этим, я всегда думал, что даты yyyy-mm-dd будут обрабатываться правильно, независимо от настроек языка/локали.

+8
источник

Старый вопрос... Но я хочу добавить что-то, что уже никто не сказал здесь... (Примечание: это мое собственное наблюдение, поэтому не просите никаких ссылок)

Datetime2 быстрее при использовании в критериях фильтра.

TL;DR:

В SQL 2016 у меня была таблица со ста тысячами строк и столбец datetime ENTRY_TIME, потому что требовалось хранить точное время до нескольких секунд. Выполняя сложный запрос со многими объединениями и подзапросом, когда я использовал where where как:

WHERE ENTRY_TIME >= '2017-01-01 00:00:00' AND ENTRY_TIME < '2018-01-01 00:00:00'

Запрос был прекрасным изначально, когда было сотни строк, но когда число строк увеличилось, запрос начал давать эту ошибку:

Execution Timeout Expired. The timeout period elapsed prior
to completion of the operation or the server is not responding.

Я удалил предложение where, и неожиданно запрос был запущен за 1 секунду, хотя теперь были выбраны ВСЕ строки для всех дат. Я запускаю внутренний запрос с предложением where, и это заняло 85 секунд, и без предложения where это заняло 0.01 сек.

Я столкнулся со многими темами для этой проблемы как производительность фильтрации datetime

Я немного оптимизировал запрос. Но реальная скорость, которую я получил, - это изменить столбец datetime на datetime2.

Теперь тот же самый запрос, который был вычислен ранее, занимает менее секунды.

веселит

+7
источник

Согласно этой статье, если вы хотите иметь одинаковую точность DateTime с использованием DateTime2, вы просто должны использовать DateTime2 (3). Это должно дать вам ту же точность, занимать на один байт меньше и обеспечить расширенный диапазон.

+5
источник

Я просто наткнулся на еще одно преимущество для DATETIME2: он избегает ошибки в модуле Python adodbapi, который взрывается, если передается стандартное значение библиотеки datetime, которое имеет ненулевые микросекунды для datetime, но отлично работает, если столбец определяется как DATETIME2.

+4
источник

Я думаю, что DATETIME2 - лучший способ сохранить date, потому что он имеет большую эффективность, чем DATETIME. В SQL Server 2008 вы можете использовать DATETIME2, он хранит дату и время, занимает 6-8 bytes для хранения и имеет точность 100 nanoseconds. Так что любой, кому нужна большая точность времени, захочет DATETIME2.

0
источник
Select ValidUntil + 1
from Documents

Вышеуказанный SQL не будет работать с полем DateTime2. Он возвращается и ошибка "Столкновение типа Operand: datetime2 несовместимо с int"

Добавление 1 для получения следующего дня - это то, что разработчики делали с датами в течение многих лет. Теперь у Microsoft есть супер новое поле datetime2, которое не может справиться с этой простой функциональностью.

"Используйте этот новый тип, который хуже старого", я так не думаю!

0
источник

Посмотрите другие вопросы по меткам или Задайте вопрос