SQL Server의 DateTime2 대 DateTime
어느 것:
SQL Server 2008+에서 날짜와 시간을 저장 하는 데 권장되는 방법 은 무엇입니까?
정밀도 (그리고 아마도 저장 공간)의 차이를 알고 있지만 지금은이를 무시하고 언제 무엇을 사용해야하는지에 대한 모범 사례 문서가 datetime2있습니까 , 아니면 그냥 사용해야 할까요?
대한 MSDN 문서 날짜는 사용을 권장 DATETIME2를 . 권장 사항은 다음과 같습니다.
새 작업
time에는date,datetime2및datetimeoffset데이터 유형을 사용하십시오 . 이러한 유형은 SQL 표준과 일치합니다. 휴대 성이 더 뛰어납니다.time,datetime2및datetimeoffset초 이상 정밀도를 제공한다.datetimeoffset전역 적으로 배포 된 응용 프로그램에 대한 표준 시간대 지원을 제공합니다.
datetime2에는 더 큰 날짜 범위, 더 큰 기본 분수 정밀도 및 선택적 사용자 지정 정밀도가 있습니다. 또한 사용자 지정 정밀도에 따라 더 적은 저장 공간을 사용할 수 있습니다.
DATETIME2날짜 범위는 " DATETIME0001/01/01 "에서 " 9999/12/31 "까지이며 유형은 1753-9999 년만 지원합니다.
또한 필요한 경우 DATETIME2시간 측면에서 더 정확할 수 있습니다. DATETIME은 3 1/3 밀리 초로 제한되지만 DATETIME2100ns까지 정확할 수 있습니다.
두 유형 모두 System.DateTime.NET에서 매핑됩니다 . 차이점은 없습니다.
선택권이 있다면 DATETIME2가능할 때마다 사용하는 것이 좋습니다 . 나는 DATETIME(이전 버전과의 호환성을 제외하고) 어떤 이점도 보지 못합니다. (날짜가 범위를 벗어 났고 그런 번거 로움으로 인해) 덜 문제가 될 것입니다.
또한 날짜 만 필요하면 (시간 부분 없음) DATE를 사용하십시오. DATE만큼이나 DATETIME2공간을 절약 할 수 있습니다! :-) 같은 시간에만 사용 TIME됩니다. 그것이 바로 이러한 유형이있는 이유입니다!
datetime2 는 (이전 앱 호환성)을 제외한 대부분의 측면에서 승리합니다.
- 더 넓은 범위의 값
- 더 나은 정확도
- 더 작은 저장 공간 (선택적 사용자 지정 정밀도가 지정된 경우)
다음 사항에 유의하십시오.
- 통사론
- datetime2 [(분수 초 정밀도 => 저장소 크기 아래보기)]
- 정밀도, 규모
- 100ns의 정확도로 0 ~ 7 자리 숫자.
- 기본 정밀도는 7 자리입니다.
- 저장 크기
- 정밀도가 3 미만인 경우 6 바이트;
- 정밀도 3 및 4의 경우 7 바이트.
- 다른 모든 정밀도 에는 8 바이트가 필요합니다 .
- DateTime2(3) have the same number of digits as DateTime but uses 7 bytes of storage instead of 8 byte (SQLHINTS- DateTime Vs DateTime2)
- Find more on datetime2(Transact-SQL MSDN article)
image source : MCTS Self-Paced Training Kit (Exam 70-432): Microsoft® SQL Server® 2008 - Implementation and Maintenance Chapter 3:Tables -> Lesson 1: Creating Tables -> page 66
I concurr with @marc_s and @Adam_Poward -- DateTime2 is the preferred method moving forward. It has a wider range of dates, higher precision, and uses equal or less storage (depending on precision).
One thing the discussion missed, however...
@Marc_s states: Both types map to System.DateTime in .NET - no difference there. This is correct, however, the inverse is not true...and it matters when doing date range searches (e.g. "find me all records modified on 5/5/2010").
.NET's version of Datetime has similar range and precision to DateTime2. When mapping a .net Datetime down to the old SQL DateTime an implicit rounding occurs. The old SQL DateTime is accurate to 3 milliseconds. This means that 11:59:59.997 is as close as you can get to the end of the day. Anything higher is rounded up to the following day.
Try this :
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'
Avoiding this implicit rounding is a significant reason to move to DateTime2. Implicit rounding of dates clearly causes confusion:
- Strange datetime behavior in SQL Server
- http://bytes.com/topic/sql-server/answers/578416-weird-millisecond-part-datetime-data-sql-server-2000-a
- SQL Server 2008 and milliseconds
- 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
Almost all the Answers and Comments have been heavy on the Pros and light on the Cons. Here's a recap of all Pros and Cons so far plus some crucial Cons (in #2 below) I've only seen mentioned once or not at all.
- PROS:
1.1. More ISO compliant (ISO 8601) (although I don’t know how this comes into play in practice).
1.2. More range (1/1/0001 to 12/31/9999 vs. 1/1/1753-12/31/9999) (although the extra range, all prior to year 1753, will likely not be used except for ex., in historical, astronomical, geologic, etc. apps).
1.3. Exactly matches the range of .NET’s DateTime Type’s range (although both convert back and forth with no special coding if values are within the target type’s range and precision except for Con # 2.1 below else error / rounding will occur).
1.4. More precision (100 nanosecond aka 0.000,000,1 sec. vs. 3.33 millisecond aka 0.003,33 sec.) (although the extra precision will likely not be used except for ex., in engineering / scientific apps).
1.5. When configured for similar (as in 1 millisec not "same" (as in 3.33 millisec) as Iman Abidi has claimed) precision as DateTime, uses less space (7 vs. 8 bytes), but then of course, you’d be losing the precision benefit which is likely one of the two (the other being range) most touted albeit likely unneeded benefits).
- CONS:
2.1. When passing a Parameter to a .NET SqlCommand, you must specify System.Data.SqlDbType.DateTime2 if you may be passing a value outside the SQL Server DateTime’s range and/or precision, because it defaults to System.Data.SqlDbType.DateTime.
2.2. Cannot be implicitly / easily converted to a floating-point numeric (# of days since min date-time) value to do the following to / with it in SQL Server expressions using numeric values and operators:
2.2.1. add or subtract # of days or partial days. Note: Using DateAdd Function as a workaround is not trivial when you're needing to consider multiple if not all parts of the date-time.
2.2.2. take the difference between two date-times for purposes of “age” calculation. Note: You cannot simply use SQL Server’s DateDiff Function instead, because it does not compute age as most people would expect in that if the two date-times happens to cross a calendar / clock date-time boundary of the units specified if even for a tiny fraction of that unit, it’ll return the difference as 1 of that unit vs. 0. For example, the DateDiff in Day’s of two date-times only 1 millisecond apart will return 1 vs. 0 (days) if those date-times are on different calendar days (i.e. “1999-12-31 23:59:59.9999999” and “2000-01-01 00:00:00.0000000”). The same 1 millisecond difference date-times if moved so that they don’t cross a calendar day, will return a “DateDiff” in Day’s of 0 (days).
2.2.3. take the Avg of date-times (in an Aggregate Query) by simply converting to “Float” first and then back again to DateTime.
NOTE: To convert DateTime2 to a numeric, you have to do something like the following formula which still assumes your values are not less than the year 1970 (which means you’re losing all of the extra range plus another 217 years. Note: You may not be able to simply adjust the formula to allow for extra range because you may run into numeric overflow issues.
25567 + (DATEDIFF(SECOND, {d '1970-01-01'}, @Time) + DATEPART(nanosecond, @Time) / 1.0E + 9) / 86400.0 – Source: “ https://siderite.blogspot.com/2015/08/how-to-translate-t-sql-datetime2-to.html “
Of course, you could also Cast to DateTime first (and if necessary back again to DateTime2), but you'd lose the precision and range (all prior to year 1753) benefits of DateTime2 vs. DateTime which are prolly the 2 biggest and also at the same time prolly the 2 least likely needed which begs the question why use it when you lose the implicit / easy conversions to floating-point numeric (# of days) for addition / subtraction / "age" (vs. DateDiff) / Avg calcs benefit which is a big one in my experience.
Btw, the Avg of date-times is (or at least should be) an important use case. a) Besides use in getting average duration when date-times (since a common base date-time) are used to represent duration (a common practice), b) it’s also useful to get a dashboard-type statistic on what the average date-time is in the date-time column of a range / group of Rows. c) A standard (or at least should be standard) ad-hoc Query to monitor / troubleshoot values in a Column that may not be valid ever / any longer and / or may need to be deprecated is to list for each value the occurrence count and (if available) the Min, Avg and Max date-time stamps associated with that value.
DateTime2 wreaks havoc if you are an Access developer trying to write Now() to the field in question. Just did an Access -> SQL 2008 R2 migration and it put all the datetime fields in as DateTime2. Appending a record with Now() as the value bombed out. It was okay on 1/1/2012 2:53:04 PM, but not on 1/10/2012 2:53:04 PM.
Once character made the difference. Hope it helps somebody.
Here is an example that will show you the differences in storage size (bytes) and precision between smalldatetime, datetime, datetime2(0), and 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
which returns
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
So if I want to store information down to the second - but not to the millisecond - I can save 2 bytes each if I use datetime2(0) instead of datetime or datetime2(7).
Interpretation of date strings into datetime and datetime2 can be different too, when using non-US DATEFORMAT settings. E.g.
set dateformat dmy
declare @d datetime, @d2 datetime2
select @d = '2013-06-05', @d2 = '2013-06-05'
select @d, @d2
This returns 2013-05-06 (i.e. May 6) for datetime, and 2013-06-05 (i.e. June 5) for datetime2. However, with dateformat set to mdy, both @d and @d2 return 2013-06-05.
The datetime behavior seems at odds with the MSDN documentation of SET DATEFORMAT which states: Some character strings formats, for example ISO 8601, are interpreted independently of the DATEFORMAT setting. Obviously not true!
Until I was bitten by this, I'd always thought that yyyy-mm-dd dates would just be handled right, regardless of the language / locale settings.
while there is increased precision with datetime2, some clients doesn't support date, time, or datetime2 and force you to convert to a string literal. Specifically Microsoft mentions "down level" ODBC, OLE DB, JDBC, and SqlClient issues with these data types and has a chart showing how each can map the type.
If value compatability over precision, use datetime
Old Question... But I want to add something not already stated by anyone here... (Note: This is my own observation, so don't ask for any reference)
Datetime2 is faster when used in filter criteria.
TLDR:
In SQL 2016 I had a table with hundred thousand rows and a datetime column ENTRY_TIME because it was required to store the exact time up to seconds. While executing a complex query with many joins and a sub query, when I used where clause as:
WHERE ENTRY_TIME >= '2017-01-01 00:00:00' AND ENTRY_TIME < '2018-01-01 00:00:00'
The query was fine initially when there were hundreds of rows, but when number of rows increased, the query started to give this error:
Execution Timeout Expired. The timeout period elapsed prior
to completion of the operation or the server is not responding.
I removed the where clause, and unexpectedly, the query was run in 1 sec, although now ALL rows for all dates were fetched. I run the inner query with where clause, and it took 85 seconds, and without where clause it took 0.01 secs.
I came across many threads here for this issue as datetime filtering performance
I optimized query a bit. But the real speed I got was by changing the datetime column to datetime2.
Now the same query that timed out previously takes less than a second.
cheers
According to this article, if you would like to have the same precision of DateTime using DateTime2 you simply have to use DateTime2(3). This should give you the same precision, take up one fewer bytes, and provide an expanded range.
I just stumbled across one more advantage for DATETIME2: it avoids a bug in the Python adodbapi module, which blows up if a standard library datetime value is passed which has non-zero microseconds for a DATETIME column but works fine if the column is defined as DATETIME2.
Select ValidUntil + 1
from Documents
The above SQL won't work with a DateTime2 field. It returns and error "Operand type clash: datetime2 is incompatible with int"
다음 날을 얻기 위해 1을 더하는 것은 개발자가 수년간 날짜로해온 일입니다. 이제 Microsoft는이 간단한 기능을 처리 할 수없는 새로운 datetime2 필드를 갖게되었습니다.
"오래된 것보다 더 나쁜이 새로운 유형을 사용하자", 나는 그렇게 생각하지 않는다!
DATETIME2를 저장하는 더 좋은 방법 이라고 생각 date합니다 DATETIME.. 에서 SQL Server 2008사용할 수있는 DATETIME2, 그것은, 날짜와 시간을 저장하는 6-8 소요 bytes저장하고의 정밀도가 있습니다 100 nanoseconds. 따라서 더 큰 시간 정밀도가 필요한 사람은 누구나 DATETIME2.
참고 URL : https://stackoverflow.com/questions/1334143/datetime2-vs-datetime-in-sql-server
'programing tip' 카테고리의 다른 글
| 특정 URL을 열기 위해 브라우저에 인 텐트 보내기 (0) | 2020.09.30 |
|---|---|
| 람다 (함수) 란? (0) | 2020.09.30 |
| JavaScript로 브라우저 뷰포트 크기 가져 오기 (0) | 2020.09.30 |
| 사전을 복사하고 사본 만 편집하는 방법 (0) | 2020.09.30 |
| JavaScript에 제공된 범위 내에서 범위를 생성하는 "range ()"와 같은 메서드가 있습니까? (0) | 2020.09.30 |