DateTime2 vs DateTime w SQL Server

DATETIME2 mA Zakres daty "0001 / 01 / 01 "through" 9999 / 12 / 31 " podczas gdy typ DATETIME obsługuje tylko rok 1753-9999.

Również, jeśli trzeba, DATETIME2 może być bardziej precyzyjny pod względem czasu; DATETIME jest ograniczony do 3 1/3 milisekund, podczas gdy DATETIME2 może być dokładny do 100ns.

Oba typy mapują się do System.DateTime W. NET - nie ma różnicy.

Jeśli masz wybór, polecam użycie DATETIME2 w miarę możliwości. Nie widzę żadnych korzyści z używania DATETIME (poza kompatybilność wsteczna) - będziesz miał mniej problemów(z datami poza zasięgiem i takimi kłopotami).

Plus: jeśli potrzebujesz tylko daty (Bez części czasu), użyj daty - jest tak samo dobra jak {[0] } i oszczędza miejsce! :- ) To samo dotyczy tylko czasu-użyj TIME. Po to są te typy!

Datetime2 wygrywa w większości aspektów z wyjątkiem (kompatybilność starych aplikacji)

1. większy zakres wartości
2. lepsza Dokładność
3. smaller storage space (jeśli podana jest opcjonalna precyzja użytkownika)

Zwróć uwagę na następujące punkty

• składnia
  • datetime2[(dokładność sekund ułamkowych=> spójrz poniżej rozmiaru pamięci)]
• precyzja, skala
  • 0 do 7 cyfry, z dokładnością do 100ns.
  • domyślną dokładnością jest 7 cyfr.
• Rozmiar Pamięci
  • 6 bajtów dla precyzji mniejszej niż 3;
  • 7 bajtów dla precyzji 3 i 4.
  • Wszystkie inne precyzje wymagają 8 bajtów .
• DateTime2(3) ma taką samą liczbę cyfr jak DateTime, ale używa 7 bajtów pamięci zamiast 8 bajtów (Sqlhints - DateTime Vs DateTime2)
• Znajdź więcej na datetime2(Transact-SQL Artykuł MSDN)

Źródło obrazu : Microsoft® SQL Server® 2008-implementacja i utrzymanie systemu Microsoft ® SQL Server ® 2008]} Rozdział 3: tabele -> Lekcja 1: Tworzenie tabel - > strona 66

Używam @ marc_s i @ Adam_Poward -- DateTime2 jest preferowaną metodą. Ma szerszy zakres dat, większą precyzję i wykorzystuje równe lub mniejsze Przechowywanie (w zależności od precyzji).

Jedna rzecz, której jednak nie było w dyskusji...
@ Marc_s pisze: Both types map to System.DateTime in .NET - no difference there. Jest to poprawne, jednak odwrotność nie jest prawdziwa ...i ma to znaczenie przy przeszukiwaniu zakresu dat (np. "find me all records modified on 5/5/2010").

. NET w wersji Datetime ma podobny zakres i precyzję do DateTime2. Przy mapowaniu.Net Datetime do starego SQL DateTime dochodzi do niejawnego zaokrąglenia . Stary SQL DateTime jest dokładny do 3 milisekund. Oznacza to, że 11:59:59.997 jest tak blisko, jak można dostać się do końca dnia. Wszystko wyższe jest zaokrąglane do następnego dnia.

Spróbuj tego:

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'

Unikanie tego niejawnego zaokrąglania jest istotnym powodem, aby przejść do DateTime2. Niejawne zaokrąglanie dat wyraźnie powoduje "confusion": {]}

• dziwne zachowanie datetime w SQL Server
• http://bytes.com/topic/sql-server/answers/578416-weird-millisecond-part-datetime-data-sql-server-2000-a
• SQL Server 2008 i milisekundy
• http://improve.dk/archive/2011/06/16/getting-bit-by-datetime-rounding-or-why-235959-999-ltgt.aspx
• http://milesquaretech.com/Blog/post/2011/09/12/DateTime-vs-DateTime2-SQL-is-Rounding-My-999-Milliseconds!.aspx

DateTime2 sieje spustoszenie, jeśli jesteś programistą dostępu próbującym napisać now () do danego pola. Po prostu zrobiłem migrację Access -> SQL 2008 R2 i umieściłem wszystkie pola datetime jako DateTime2. Dołączenie rekordu z Now () jako wartość. - 1/1/2012 14:53:04

Raz postać zrobiła różnicę. Mam nadzieję, że to komuś pomoże.

Oto przykład, który pokaże różnice w rozmiarze pamięci (bajtów) i precyzji pomiędzy smalldatetime, datetime, datetime2(0) i 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

Który zwraca

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

Więc jeśli chcę zapisać informacje do sekundy-ale nie do milisekundy - mogę zapisać 2 bajty każdy, Jeśli używam datetime2(0) zamiast datetime lub datetime2 (7).

Prawie wszystkie odpowiedzi i komentarze były ciężkie na plusy i lekkie na minusy. Oto podsumowanie wszystkich plusów i minusów do tej pory plus kilka istotnych minusów (w #2 poniżej) widziałem wymienione tylko raz lub wcale.

1. plusy:

1.1. Bardziej zgodny z ISO (ISO 8601) (chociaż Nie wiem, jak to wchodzi w grę w praktyce).

1.2. Większy zakres (1/1/0001 do 12/31/9999 vs. 1/1/1753-12/31/9999) (chociaż dodatkowy zakres, Wszystkie przed rokiem 1753, prawdopodobnie nie będzie używany z wyjątkiem ex., w zakresie historycznym, astronomicznym, geologicznym itp. aplikacje).

1.3. Dokładnie odpowiada zakresowi typu. net DateTime (chociaż oba konwertują tam iz powrotem bez specjalnego kodowania, jeśli wartości mieszczą się w zakresie i precyzji typu docelowego, z wyjątkiem Con # 2.1 poniżej, wystąpi błąd / zaokrąglenie).

1.4. Więcej precyzji (100 nanosekundy aka 0.000, 000, 1 sec. vs. 3.33 milisekundy aka 0.003, 33 sec.) (chociaż dodatkowa precyzja będzie prawdopodobnie nie może być używany z wyjątkiem ex., w aplikacjach inżynierskich / naukowych).

1.5. Po skonfigurowaniu dla podobnego (jak w 1 miliseku, a nie "takiego samego" (jak w 3,33 miliseku), jak twierdził Iman Abidi) precyzja jako DateTime, zużywa mniej miejsca (7 na 8 bajtów), ale oczywiście stracisz korzyść z precyzji, która jest prawdopodobnie jedną z dwóch (druga to Zakres) najbardziej reklamowanych, choć prawdopodobnie niepotrzebnych korzyści).

1. wady:

2.1. Podczas przechodzenia a Parametr do. Net SqlCommand, musisz określić System.Data.SqlDbType.DateTime2, jeśli możesz przekazywać wartość poza zakres i/lub precyzję serwera SQL DateTime, ponieważ domyślnie jest to System.Data.SqlDbType.DateTime.

2.2. Nie można w sposób niejawny / łatwy przekonwertować na zmiennoprzecinkową wartość liczbową (#dni od daty min), aby wykonać następujące czynności w wyrażeniach SQL Server przy użyciu wartości liczbowych i operatorów: {]}

2.2.1. Dodaj lub odejmuj # dni lub częściowe dni. Uwaga: użycie funkcji DateAdd jako obejścia jest nie trywialne, gdy musisz rozważyć wiele, jeśli nie wszystkie części daty-czasu.

2.2.2. weź różnicę między dwiema datami dla celów obliczenia "wieku". Uwaga: nie możesz po prostu użyć funkcji DateDiff SQL Server zamiast tego, ponieważ nie oblicza age, jak większość ludzi by się spodziewała, że jeśli dwie daty przekroczą granicę daty kalendarz / zegar dla określonych jednostek, nawet dla niewielkiego ułamka tej jednostki, zwróci różnicę jako 1 tej jednostki VS. 0. Na przykład, DateDiff w Day'S z dwóch dat-razy tylko 1 milisekundy od siebie zwróci 1 vs. 0 (dni), jeśli te daty-razy są w różnych dniach kalendarzowych (tj. "1999-12-31 23:59:59.9999999" i "2000-01-01 00:00:00.0000000"). Ta sama 1 milisekundowa różnica date-times jeśli przesunięta tak, aby nie przekraczała dnia kalendarzowego, zwróci "DateDiff" w Day ' S z 0 (dni).

2.2.3. weź Avg z date-times (w zapytaniu zbiorczym), po prostu konwertując na Najpierw "Float", a następnie z powrotem do DateTime.

UWAGA: Aby przekonwertować DateTime2 na liczbę, musisz zrobić coś takiego jak poniższy wzór, który nadal zakłada, że twoje wartości są nie mniejsze niż rok 1970 (co oznacza, że tracisz cały dodatkowy zakres plus kolejne 217 lat. Uwaga: możesz nie być w stanie po prostu dostosować formułę, aby zezwolić na dodatkowy zakres, ponieważ możesz napotkać problemy z przepełnieniem liczb.

25567 + (DATEDIFF(SECOND, {d '1970-01-01'}, @Time) + DATEPART(nanosecond, @Time) / 1.0E + 9) / 86400.0 – źródło: " https://siderite.blogspot.com/2015/08/how-to-translate-t-sql-datetime2-to.html "

Oczywiście, możesz również Cast do DateTime jako pierwszy (i w razie potrzeby z powrotem do DateTime2), ale stracisz precyzję i zakres (wszystkie sprzed roku 1753) korzyści DateTime2 VS. DateTime które są prolly 2 największe i jednocześnie prolly 2 najmniej prawdopodobne potrzebne, co rodzi pytanie, dlaczego go używać, gdy stracisz Ukryte / łatwe konwersje do liczb zmiennoprzecinkowych (#dni) do dodawania / odejmowania / "wieku" (vs.DateDiff) / Avg korzyści z kalkulacji, które są duże z mojego doświadczenia.

Btw, Avg of date-times jest (lub przynajmniej powinien być) ważnym przypadkiem użycia. a) oprócz użycia w uzyskiwaniu średniego czasu trwania, gdy daty-czasy (od wspólnej daty bazowej) są używane do reprezentowania czasu trwania (powszechna praktyka), b) przydatne jest również uzyskanie statystyki typu Pulpitu nawigacyjnego na temat tego, jaka jest średnia Data-Czas w kolumnie Data-czas zakresu / grupy wierszy. c) A standardowe (lub co najmniej powinno być standardem) zapytanie ad-hoc do monitorowania / rozwiązywania problemów wartości w kolumnie, która może nie być ważna zawsze / dłużej i / lub może być przestarzała, to lista dla każdej wartości liczba wystąpień i (jeśli jest dostępna) Min, Avg i Max znaczniki daty i czasu związane z tą wartością.

Interpretacja łańcuchów daty na datetime i datetime2 może być również inna, jeśli używasz ustawień innych niż US DATEFORMAT. Np.

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

Zwraca 2013-05-06 (tj. 6 maja) dla datetime i 2013-06-05 (tj. 5 czerwca) dla datetime2. Jednak z dateformat ustawionym na mdy, zarówno @d, jak i @d2 zwracają 2013-06-05.

Zachowanie datetime wydaje się sprzeczne z dokumentacją MSDN z SET DATEFORMAT, która stwierdza: niektóre formaty ciągów znaków, na przykład ISO 8601, są interpretowane niezależnie od Ustawienie DATEFORMAT . Oczywiście, że nie!

Dopóki nie zostałem ugryziony przez to, zawsze myślałem, że yyyy-mm-dd daty będą po prostu obsługiwane prawidłowo, niezależnie od ustawień języka / locale.

Chociaż istnieje zwiększona precyzja z datetime2 , niektórzy klienci nie obsługują date, Czas , lub datetime2 i zmusić cię do konwersji na literalny ciąg znaków. W szczególności Microsoft wspomina o problemach" down level " ODBC, OLE DB, JDBC i SqlClient z tymi typami danych i ma Wykres pokazujący, w jaki sposób każdy może odwzorować Typ.

If value compatability over precision, use datetime

Zgodnie z Ten artykuł, jeśli chcesz mieć taką samą precyzję DateTime używając DateTime2, musisz po prostu użyć DateTime2 (3). Powinno to zapewnić taką samą precyzję, zająć o jeden bajt mniej i zapewnić rozszerzony zakres.

Właśnie natknąłem się na jeszcze jedną zaletę dla DATETIME2: unika błędu w module Pythona adodbapi, który wybucha, jeśli przekazana zostanie wartość standardowej biblioteki datetime, która ma niezerowe mikrosekundy dla kolumny DATETIME, ale działa dobrze, jeśli kolumna jest zdefiniowana jako DATETIME2.

Stare Pytanie... Ale chcę dodać coś, czego jeszcze nikt tu nie stwierdził... (Uwaga: to jest moja własna obserwacja, więc nie proś o żadne odniesienie)

Datetime2 jest szybszy, gdy jest używany w kryteriach filtrowania.

TLDR:

W SQL 2016 miałem tabelę ze stu tysiącami wierszy i kolumnę DateTime ENTRY_TIME, ponieważ wymagane było przechowywanie dokładnego czasu do sekund. Podczas wykonywania złożonego zapytania z wieloma połączeniami i zapytaniem podrzędnym, gdy użyłem gdzie klauzula as:

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

Zapytanie było dobre początkowo, gdy były setki wierszy, ale gdy liczba wierszy wzrosła, zapytanie zaczęło podawać ten błąd:

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

Usunąłem klauzulę where i nieoczekiwanie, zapytanie zostało uruchomione w 1 sek, chociaż teraz wszystkie wiersze dla wszystkich dat zostały pobrane. Uruchamiam wewnętrzne zapytanie z klauzulą where I zajęło to 85 sekund, a bez klauzuli where zajęło 0.01 sekund.

Natknąłem się tutaj na wiele wątków dla tego problemu jako datetime wydajność filtrowania

Trochę zoptymalizowałem zapytanie. Ale prawdziwa prędkość, jaką uzyskałem, to zmiana kolumny datetime na datetime2.

Teraz to samo zapytanie, które wcześniej trwało mniej niż sekundę.

Cheers

Select ValidUntil + 1
from Documents

Powyższy SQL nie będzie działał z polem DateTime2. Zwraca i błąd "Typ operandu clash: datetime2 jest niezgodny z int"

Dodanie 1, aby dostać następnego dnia jest czymś, co deweloperzy robią z datami od lat. Teraz Microsoft ma super nowe pole datetime2, które nie może obsłużyć tej prostej funkcjonalności.

"użyjmy tego nowego typu, który jest gorszy od Starego", nie sądzę!

Myślę, że DATETIME2 jest lepszym sposobem przechowywania daty, ponieważ ma większą wydajność niż DATETIME. W SQL Server 2008 można użyć DATETIME2, przechowuje datę i czas, zajmuje 6-8 bajtów do przechowywania i ma dokładność 100 nanosekund. Więc każdy, kto potrzebuje większej precyzji czasu, będzie chciał DATETIME2.