DateTime2 versus DateTime in SQL Server

Welke:

• DATETIME
• DATETIME2

is deaanbevolen manier om datum en tijd op te slaan in SQL Server 2008+?

Ik ben me bewust van verschillen in precisie (en waarschijnlijk opslagruimte), maar als ik die voor nu negeer, is er een best practice-document over wanneer wat te gebruiken, of misschien moeten we gewoon DATETIME2gebruiken alleen?

Antwoord 1, autoriteit 100%

In de MSDN-documentatie voor datetimewordt het gebruik van datetime2. Dit is hun aanbeveling:

Gebruik de TIME, date, DATETIME2en
datetimeoffsetgegevenstypen voor nieuwe
werk. Deze typen komen overeen met de SQL
Standaard. Ze zijn draagbaarder.
TIME, DATETIME2en datetimeoffset
bieden meer seconden precisie.
datetimeoffsetgeeft tijdzone
ondersteuning voor wereldwijd ingezet
toepassingen.

datetime2 heeft een groter datumbereik, een grotere standaard fractionele precisie en optionele door de gebruiker gespecificeerde precisie. Ook kan het, afhankelijk van de door de gebruiker gespecificeerde precisie, minder opslagruimte gebruiken.

Antwoord 2, autoriteit 77%

DATETIME2heeft een datumbereik van “0001 / 01 / 01” tot en met “9999 / 12 / 31”, terwijl het type DATETIMEalleen jaar 1753-9999 ondersteunt.

Als dat nodig is, kan DATETIME2ook nauwkeuriger zijn in termen van tijd; DATETIME is beperkt tot 3 1/3 milliseconden, terwijl DATETIME2nauwkeurig kan zijn tot 100ns.

Beide typen verwijzen naar System.DateTimein .NET – daar is geen verschil.

Als je de keuze hebt, raad ik aan om waar mogelijk DATETIME2te gebruiken. Ik zie geen voordelen bij het gebruik van DATETIME(behalve voor achterwaartse compatibiliteit) – u zult minder problemen hebben (met datums die buiten bereik zijn en dat soort gedoe).

Plus: als je alleen de datum nodig hebt (zonder tijdsgedeelte), gebruik dan DATE – het is net zo goed als DATETIME2en bespaart je ook nog eens ruimte! 🙂 Hetzelfde geldt alleen voor tijd – gebruik TIME. Daar zijn deze types voor!

Antwoord 3, autoriteit 33%

datetime2wint in de meeste aspecten behalve (compatibiliteit van oude apps)

1. groter waardenbereik
2. betere Nauwkeurigheid
3. kleinere opslagruimte(als optionele door de gebruiker opgegeven precisie is opgegeven)

let op de volgende punten

• Syntaxis
  • datetime2[(fractionele seconden precisie=> Kijk onder opslaggrootte)]
• Precisie, schaal
  • 0 tot 7 cijfers, met een nauwkeurigheid van 100ns.
  • De standaardprecisie is 7 cijfers.
• Opslaggrootte
  • 6 bytes voor precisie van minder dan 3;
  • 7 bytes voor precisie 3 en 4.
  • Alle andere precisie vereisen 8 bytes.
• Datetime2 (3) hebben hetzelfde aantal cijfers als DateTime, maar gebruikt 7 bytes of opslag in plaats van 8 byte (sqlhints- datetime vs datetime2 )
• Zoek meer op Datetime2 (transact-sql MSDN-artikel)

Beeldbron:
MCTS Self-Paced Training Kit (examen 70-432): Microsoft® SQL SERVER® 2008 – Implementatie en onderhoud
Hoofdstuk 3: Tafels – & GT; LES 1: Tafels maken – & GT; Page 66

4, Autoriteit 16%

I Concurr met @marc_s en @Adam_POWARD – Datetime2 is de voorkeursmethode die vooruit is. Het heeft een breder scala aan data, hogere precisie en gebruikt gelijke of minder opslag (afhankelijk van precisie).

Eén ding De discussie miste, hoe …
@Marc_s Staten: Both types map to System.DateTime in .NET - no difference there. Dit is correct, De inverse is echter niet waar … en het is belangrijk bij zoekopdrachten op datumbereik (bijvoorbeeld “vind me alle records gewijzigd op 5/5/2010”).

.NET’s versie van DATETIMEheeft een vergelijkbaar bereik en precisie op DATETIME2. Bij het toewijzen van een .NET DATETIMEDOWN TOT DE OUDE SQL DATETIMEA Impliciete afronding vindt . De oude SQL DATETIMEis correct op 3 milliseconden. Dit betekent dat 11:59:59.997zo dichtbij is als u aan het einde van de dag kunt komen. Iets hoger is afgerond tot de volgende dag.

Probeer dit:

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'

Dit impliciete afronding vermijden is een belangrijke reden om naar DateTime2 te gaan2. Impliciete afronding van de data veroorzaakt duidelijk verwarring:

• Vreemd DateDime-gedrag in SQL Server
• http://bytes.com/topic/sql-server/answers/578416-weird-millisecond-part-datetime-data-sql-server-2000-a
• SQL Server 2008 en milliseconden
• http://improve.dk/Archive/2011/06/16/getting-bit-by-datetime-round-or-why-235959-999-ltgt.aspx
• http://milesquaretech.com/blog/post/2011/09/12/datetime-vs-datetime2-sql-is-round-my-999-milliseconds !.aspx

5, Autoriteit 4%

Bijna alle antwoorden en opmerkingen zijn zwaar op de voor- en licht op de nadelen. Hier is een samenvatting van alle voor- en nadelen tot nu toe plus een aantal cruciale nadelen (in # 2 hieronder) die ik slechts één keer heb gezien of helemaal niet.

1. PROS:

1.1. Meer ISO-conform (ISO 8601) (hoewel ik niet weet hoe dit in de praktijk in de praktijk komt).

1.2. Meer bereik (1/1/0001 tot 31/12/9999 vs. 1/1/1753-12/31/9999) (hoewel het extra bereik, allemaal vóór het jaar 1753, waarschijnlijk niet zal worden gebruikt, behalve bijv., in historische, astronomische, geologische, etc. apps).

1.3. Komt exact overeen met het bereik van het DATETIMEType-bereik van .NET (hoewel beide heen en weer worden geconverteerd zonder speciale codering als waarden binnen het bereik en de precisie van het doeltype vallen, behalve voor Con # 2.1 hieronder, anders zal er een fout / afronding optreden ).

1.4. Meer precisie (100 nanoseconde oftewel 0.000.000,1 sec. vs. 3.33 milliseconde oftewel 0.003,33 sec.) (hoewel de extra precisie waarschijnlijk niet zal worden gebruikt, behalve bijvoorbeeld in technische / wetenschappelijke apps).

1.5. Wanneer geconfigureerd voor vergelijkbaar(zoals in 1 millisec niet “hetzelfde” (zoals in 3,33 millisec) zoals Iman Abidi beweerde), gebruikt precisie als DATETIMEminder ruimte (7 vs. 8 bytes), maar dan verlies je natuurlijk het precisievoordeel dat waarschijnlijk een van de twee is (de andere is bereik) die het meest wordt aangeprezen, hoewel waarschijnlijk onnodige voordelen).

2. NADELEN:

2.1. Wanneer u een parameter doorgeeft aan een .NET SqlCommand, moet u System.Data.SqlDbType.DateTime2opgeven als u een waarde wilt doorgeven buiten de SQL Server DATETIME‘s bereik en/of precisie, omdat het standaard System.Data.SqlDbType.DateTimeis.

2.2. Kan niet impliciet / gemakkelijk worden geconverteerd naar een numerieke waarde met drijvende komma (# dagen sinds min datum-tijd) om het volgende te doen / ermee in SQL Server-expressies met behulp van numerieke waarden en operators:

2.2.1. # dagen of gedeeltelijke dagen optellen of aftrekken. Opmerking: het gebruik van de functie DATEADDals tijdelijke oplossing is niet triviaal wanneer u meerdere, zo niet alle delen van de datum-tijd moet overwegen.

2.2.2. Neem het verschil tussen twee datum-tijden voor doeleinden van de berekening van “Age”. OPMERKING: U kunt SQLL Server’s DATEDIFF-functie niet eenvoudig gebruiken, omdat deze niet wordt berekend ageZoals de meeste mensen zouden verwachten als de twee datum-tijden overtreden een kalender / Klokdatum-tijdgrens van de gespecificeerde eenheden als, zelfs voor een kleine fractie van die eenheid, het zal het verschil retourneren als 1 van die eenheid versus 0. Bijvoorbeeld de DATEDIFFIN Day‘s van twee datum-tijden slechts 1 millisecond apart zal 1 vs. 0 (dagen) terugkeren als die datum-tijden op verschillende kalenderdagen zijn (dat wil zeggen “1999-12-31 23: 59: 59.9999999” en “2000-01-01 00: 00: 00.0000000”). Dezelfde 1 milliseconde verschil-datum-tijden indien verplaatst, zodat ze geen kalenderdag kruisen, een “dateiff” in Days van 0 (dagen) retourneren.

2.2.3. Neem de Avgvan datum-tijden (in een geaggregeerde query) door simpelweg te converteren naar “float” eerst en vervolgens weer terug naar DATETIME.

OPMERKING: Om DATETIME2naar een numeriek te converteren, moet u iets doen als de volgende formule die nog steeds aanneemt dat uw waarden niet minder zijn dan het jaar 1970 (wat betekent dat u alles wat betreft Extra bereik plus nog eens 217 jaar. Opmerking: u kunt de formule niet eenvoudig aanpassen om extra bereik mogelijk te maken, omdat u in numerieke overloopproblemen kunt uitvoeren.

25567 + (DATEDIFF(SECOND, {d '1970-01-01'}, @Time) + DATEPART(nanosecond, @Time) / 1.0E + 9) / 86400.0– Bron: “https://siderite.dev/blog/how- To-Translate-T-SQL-datetime2-to.html “

Natuurlijk kunt u ook eerst Castnaar DATETIME(en indien nodig weer terug naar DATETIME2), maar dan verliest u de precisie en bereik (allemaal vóór het jaar 1753) voordelen van DATETIME2versus DATETIMEdie waarschijnlijk de 2 grootste zijn en tegelijkertijd ook de 2 minst waarschijnlijke nodig die smeekt de vraag waarom het gebruiken als je de impliciete / gemakkelijke conversies verliest naar drijvende-komma numeriek (# dagen) voor optellen / aftrekken / “leeftijd” (vs. DATEDIFF) / Avgcalcs-voordeel, wat in mijn ervaring een grote is.

Trouwens, de Avgvan datum-tijden is (of op zijn minst zou moetenzijn) een belangrijke use case. a) Naast het gebruik om de gemiddelde duur te krijgen wanneer datum-tijden (sinds een gemeenschappelijke basisdatum-tijd) worden gebruikt om de duur weer te geven (een gangbare praktijk), b) is het ook handig om een statistiek van het dashboardtype te krijgen over wat de gemiddelde datum- tijd staat in de datum-tijdkolom van een bereik / groep rijen. c) Een standaard (of op zijn minst moetstandaard zijn) ad-hoc Query om waarden in een kolom te controleren/op te lossen die mogelijk niet/nooit meer geldig zijn en/of die mogelijk moeten worden afgeschaft, is om vermeld voor elke waarde het aantal exemplaren en (indien beschikbaar) de Min, Avgen Maxdatum-tijdstempels die bij die waarde horen.

Antwoord 6, autoriteit 2%

Hier is een voorbeeld dat u de verschillen in opslaggrootte (bytes) en precisie laat zien tussen smalldatetime, datetime, datetime2(0) en 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

die retourneert

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

Dus als ik informatie wil opslaan op de tweede – maar niet op de milliseconde – kan ik 2 bytes besparen als ik DateTime2 (0) gebruik in plaats van datetime of datetime2 (7).

7, Autoriteit 2%

DateTime2 Wreast HAVOC als u een toegangsontwikkelaar bent die nu probeert () naar het betreffende veld te schrijven. Deed gewoon een toegang – & GT; SQL 2008 R2 MIGRATIE EN HET ZETT ALLE DATETIME-velden in AS DATETIME2. Een record met nu () toevoegen als de waarde die is gebombardeerd. Het was ok op 1/1/2012 2:53:04 PM, maar niet op 1/10/2012 2:53:04 PM.

Zodra karakter het verschil heeft gemaakt. Ik hoop dat het iemand helpt.

8, Autoriteit 2%

Oude vraag … maar ik wil iets toevoegen dat niet door iedereen hier is vermeld … (Opmerking: dit is mijn eigen observatie, dus vraag niet om een ​​referentie)

Datetime2 is sneller bij gebruik in filtercriteria.

TLDR:

In SQL 2016 had ik een tafel met honderdduizend rijen en een DateDime-kolom entime omdat het nodig was om de exacte tijd maximaal seconden op te slaan. Tijdens het uitvoeren van een complexe query met veel joins en een subquery, toen ik gebruikte waar clausule als:

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

De query was in eerste instantie prima toen er honderden rijen waren, maar toen het aantal rijen toegenomen, begon de query deze fout te geven:

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

Ik heb de waarheid verwijderd en onverwachts, de query werd uitgevoerd in 1 seconde, hoewel nu alle rijen voor alle datums werden opgehaald. Ik run de innerlijke vraag met waar clausule, en het duurde 85 seconden, en zonder waar clausule 0,01 sec.

Ik kwam hier vele draden tegen voor dit probleem als DateTime-filteringsprestaties

Ik heb een beetje query geoptimaliseerd. Maar de echte snelheid die ik kreeg was door de kolom DateDime naar DateTime2 te veranderen.

Nu dezelfde query die timed duurt eerder minder dan een seconde.

Cheers

Antwoord 9

terwijl er meer precisieis met datetime2, ondersteunen sommige clients date, timeof datetime2niet en dwingen je om te converteren naar een letterlijke tekenreeks. Microsoft noemt met name “down level” ODBC-, OLE DB-, JDBC- en SqlClient-problemen met deze gegevenstypen en heeft een grafiekdie laat zien hoe elk het type in kaart kan brengen.

Als compatibiliteitbelangrijker is dan precisie, gebruik dan datetime

Antwoord 10

Volgens dit artikel, als je dezelfde precisie van DateTime wilt hebben met DateTime2 moet je gewoon DateTime2(3) gebruiken. Dit zou u dezelfde precisie moeten geven, één bytes minder in beslag nemen en een groter bereik bieden.

Antwoord 11

Ik kwam zojuist nog een voordeel tegen voor DATETIME2: het vermijdt een bug in de Python adodbapi-module, die ontploft als een standaardbibliotheek DATETIMEwaarde wordt doorgegeven die niet nul microseconden heeft voor een DATETIMEkolom, maar werkt prima als de kolom is gedefinieerd als DATETIME2.

Antwoord 12

Zoals de andere antwoorden laten zien, wordt DATETIME2aanbevolen vanwege het kleinere formaat en meer precisie, maar hier zijn enkele gedachten over waarom datetime2 van Nikola Ilic NIET gebruiken:

• gebrek aan (eenvoudige) mogelijkheid om elementaire wiskundige bewerkingen uit te voeren met datums, zoals GETDATE()+1
• elke keer dat u vergelijkingen maakt met DATEADDof DATEDIFF, eindigt u met impliciete gegevensconversie naar DATETIME
• SQL Server kan statistieken niet correct gebruiken voor Datetime2-kolommen, vanwege een manier waarop gegevens worden opgeslagen die leiden tot niet-optimale queryplannen, waardoor de prestaties afnemen

Antwoord 13

Ik denk dat DATETIME2de betere manier is om de dateop te slaan, omdat het efficiënter is dan
de DATETIME. In SQL Server 2008kun je DATETIME2gebruiken, het slaat een datum en tijd op, het duurt 6-8 bytesom op te slaan en heeft een precisie van 100 nanoseconds. Dus iedereen die meer tijdsprecisie nodig heeft, wil DATETIME2.

Antwoord 14

Geaccepteerd antwoord is geweldig, maar weet dat als je een DateTime2 naar de frontend stuurt, deze wordt afgerond naar het normale DateTime-equivalent.

Dit veroorzaakte een probleem voor mij omdat ik in een oplossing van mij moest vergelijken wat er was verzonden met wat er in de database stond toen het opnieuw werd ingediend, en mijn eenvoudige vergelijking ‘==’ stond afronding niet toe. Dus het moest worden toegevoegd.

Antwoord 15

Select ValidUntil + 1
from Documents

De bovenstaande SQL werkt niet met een DateTime2-veld.
Het geeft de foutmelding “Operand type clash: datetime2 is incompatibel met int”

Het toevoegen van 1 om de volgende dag te krijgen, is iets wat ontwikkelaars al jaren doen met datums. Nu heeft Microsoft een super nieuw datetime2-veld dat deze eenvoudige functionaliteit niet aankan.

“Laten we dit nieuwe type gebruiken dat erger is dan het oude”, ik denk het niet!