sql-server - SQL Server의 DateTime2와 DateTime 비교




tsql (13)

Select ValidUntil + 1
from Documents

위의 SQL은 DateTime2 필드에서 작동하지 않습니다. 그것은 반환하고 오류 "피연산자 유형 충돌 : datetime2는 int와 호환되지 않습니다"

다음 날을 얻기 위해 1을 더하는 것은 개발자들이 수년간 날짜를 가지고 수행해온 것입니다. 이제 Microsoft는이 간단한 기능을 처리 할 수없는 새로운 datetime2 필드를 사용합니다.

"이전 유형보다 더 나쁜 새로운 유형을 사용합시다."라고 나는 생각하지 않는다!

어느 것:

SQL Server 2008 이상에서 날짜 및 시간을 저장하는 가장 좋은 방법은 무엇입니까?

정확도 (및 저장 공간이 아마도 차이가 있음)를 알고 있지만, 지금은 무시하고, 무엇을 사용해야하는지 또는 datetime2 만을 사용해야하는지에 대한 모범 사례 문서가 있습니까?


datetime2 정밀도 가 향상되는 반면 일부 클라이언트는 날짜 , 시간 또는 datetime2를 지원하지 않으므로 문자열 리터럴로 변환해야합니다. 특히 Microsoft는 이러한 데이터 형식에 대한 ODBC, OLE DB, JDBC 및 SqlClient와 관련된 "다운 수준"에 대해 언급하고 각 데이터 형식 매핑 방법을 보여주는 datetime2 를 제공합니다.

값과의 호환성 이 정확하다면 datetime


DateTime2는 Access 개발자가 해당 필드에 Now ()를 쓰려고하면 혼란을 겪습니다. Access -> SQL 2008 R2 마이그레이션을 수행하고 모든 datetime 필드를 DateTime2로 설정합니다. 값이 폭격되면서 Now ()를 사용하여 레코드를 추가합니다. 2012 년 1 월 1 일 2:53:04 오후에 괜찮 았으나 2012 년 1 월 10 일 2시 53 분 4 초에하지 않았습니다.

일단 캐릭터가 차이를 만들었습니다. 누군가를 돕기를 바랍니다.


다음은 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

그래서 정보를 두 번째로 저장하려고한다면 - 밀리 세컨드가 아닌 - datetime 또는 datetime2 (7) 대신 datetime2 (0)를 사용하면 각각 2 바이트를 절약 할 수 있습니다.


DATETIME2 은 "0001 / 01 / 01"에서 "9999 / 12 / 31"까지의 날짜 범위를 가지며 DATETIME 유형은 1753-9999 만 지원합니다.

또한 필요한 경우 DATETIME2 는 시간면에서보다 정확할 수 있습니다. DATETIME은 3 1/3 밀리 초로 제한되며 DATETIME2는 100ns까지 정확합니다.

두 형식 모두 .NET의 System.DateTime 에 매핑됩니다. 차이는 없습니다.

선택의 여지가 있다면 가능할 때마다 DATETIME2 사용하는 것이 좋습니다. DATETIME (역 호환성을 제외하고)을 사용하면 어떤 이점도 보이지 않습니다. 날짜가 범위를 벗어나 번거롭기 때문에 문제가 덜 생깁니다.

더하기 : 날짜 만 필요하면 (시간 부분없이) DATE를 사용하십시오. DATETIME2 만큼 좋으며 공간도 절약 할 수 있습니다! :-) 같은 시간에만갑니다 - TIME 사용하십시오. 이것이 바로 이러한 유형의 요소입니다!


날짜 문자열을 datetimedatetime2 로 해석하는 것은 미국 이외의 DATEFORMAT 설정을 사용할 때도 다를 수 있습니다. 예 :

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

그러면 datetime 에 대해 2013-05-06 (즉 5 월 6 일)이 반환되고 6 월 5 일에 2013-06-05 가 반환됩니다. 그러나 dateformatmdy 설정하면 @d@d2 모두 2013-06-05 반환합니다.

datetime 동작은 SET DATEFORMATMSDN 설명서불일치합니다 . ISO 8601과 같은 일부 문자열 형식은 DATEFORMAT 설정과 별도로 해석 됩니다. 분명히 사실이 아닙니다!

내가 이것에 물릴 때까지는 언어 / 로케일 설정에 관계없이 yyyy-mm-dd 날짜가 올바르게 처리 될 것이라고 항상 생각했습니다.


이전 질문 ...하지만 여기에 다른 사람이 이미 언급하지 않은 것을 추가하고 싶습니다 ... (참고 : 이것은 내 자신의 관찰이므로 참조를 요구하지 마십시오)

Datetime2는 필터 조건에서 사용될 때 더 빠릅니다.

TLDR :

SQL 2016에서는 정확한 시간을 초까지 저장해야했기 때문에 십만 행의 테이블과 날짜 시간 열 ENTRY_TIME이있었습니다. 많은 조인과 하위 쿼리를 사용하여 복잡한 쿼리를 실행하는 동안 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로 변경하는 것입니다.

이제 이전에 시간 초과 된 동일한 쿼리가 1 초도 채 걸리지 않습니다.

건배


datetime2 는 (대부분의 응용 프로그램 호환성을 제외한) 대부분의 측면에서 승리합니다.

  1. 더 넓은 범위의 값
  2. 더 나은 정확도
  3. 더 작은 저장 공간 (선택적 사용자 지정 정밀도가 지정된 경우)

다음 사항에 유의하십시오.

  • 통사론
    • datetime2 [(분수 초 정밀도 => 저장소 크기보다 작게보기)]
  • 정밀도, 스케일
    • 0 ~ 7 자리이며 정확도는 100ns입니다.
    • 기본 정밀도는 7 자리입니다.
  • 저장 용량
    • 정밀도가 3 미만인 경우 6 바이트.
    • 정밀도 3 및 4의 경우 7 바이트
    • 다른 모든 정밀도 는 8 바이트가 필요합니다 .
  • DateTime2 (3)DateTime 과 동일한 자릿수를 갖지만 8 바이트 대신 7 바이트의 저장소를 사용합니다 ( SQLHINTS- DateTime Vs DateTime2 )
  • datetime2 에 대한 자세한 정보 (Transact-SQL MSDN 기사)

이미지 소스 : MCTS Self-Paced 교육 키트 (시험 70-432) : Microsoft® SQL Server® 2008 - 구현 및 유지 관리 3 장 : 테이블 -> 1 단원 : 테이블 만들기 -> 66 페이지


거의 모든 답변과 의견은 찬성론자와 찬성론자에게 무겁다. 여기에 모든 장단점과 단점을 요약 해 놓았습니다. 여기에 한 번 언급했거나 전혀 언급하지 않은 몇 가지 중요한 단점 (아래 2 번)이 있습니다.

  1. PROS :

1.1. 더 많은 ISO 규격 (ISO 8601) (비록 이것이 실제로 어떻게 작동하는지 모르겠지만).

1.2. 더 많은 범위 (1/1/0001 ~ 12/31/9999 대 1 / 1 / 1753-12 / 31 / 9999) (예 : 1753 년 이전의 모든 추가 범위는 ex.를 제외하고는 사용되지 않을 수도 있음) 역사적, 천문학적, 지질 학적 응용 프로그램 등).

1.3. .NET의 DateTime Type 범위의 범위와 정확하게 일치합니다 (값이 Con # 2.1을 제외한 대상 유형의 범위와 정밀도 내에 있으면 특별한 코딩없이 앞뒤로 변환되지만 그 밖의 오류 / 반올림이 발생합니다).

1.4. 더 정밀도 (100 나노초 (0.000,000,1 초) 대 3.33 밀리 초 (0.003,33 초)) (엔지니어링 / 과학 응용 프로그램에서는 예를 들어 추가 정밀도가 사용되지 않을 가능성이 있지만).

1.5. 비슷하게 (Iman Abidi가 주장한 것처럼 1 밀리 초가 "동일하지 않음"(3.33 밀리 초와 같음) DateTime 과 같은 정밀도로 구성되면 공간 (7 대 8 바이트)이 적어 지지만 물론 손실 될 수 있습니다 두 가지 중 하나 (아마도 다른 불확실한 이익 임에도 불구하고 가장 많이 선전 된 것) 중 하나 인 정확한 혜택.

  1. 단점 :

2.1. Parameter를 .NET SqlCommand 에 전달할 때 System.Data.SqlDbType.DateTime 기본값으로하기 때문에 SQL Server DateTime 의 범위 및 / 또는 정밀도를 벗어나는 값을 전달하는 경우 System.Data.SqlDbType.DateTime2 를 지정해야합니다 System.Data.SqlDbType.DateTime .

2.2. 숫자 값과 연산자를 사용하는 SQL Server 식에서 다음과 같은 작업을 수행하기 위해 암시 적으로 / 쉽게 소수점 이하 숫자 (최소 날짜 시간 이후 일수) 값으로 변환 할 수 없습니다.

2.2.1. # 일 또는 부분 일 수를 더하거나 뺍니다. 참고 : 날짜 / 시간의 모든 부분이 아니라 복수의 부분을 고려해야하는 경우 일시적인 해결책으로 DateAdd 함수를 사용하십시오.

2.2.2. "연령"계산을 위해 두 가지 날짜 - 시간의 차이를 가져옵니다. 참고 : SQL Server의 DateDiff 함수를 대신 사용할 수는 없습니다. 대부분의 사람들이 예상하는대로 날짜를 계산하지 않기 때문에 두 날짜가 달력 / 시계의 날짜 / 시간 경계를 넘는 경우 예를 들어, DateDiffDateDiff 가 1 밀리 초 밖에 걸리지 않은 두 날짜 Day 경우, DateDiff 는 1 대 0 (일)을 반환합니다. 날짜 - 시간은 다른 달력 일 (예 : "1999-12-31 23 : 59 : 59.9999999"및 "2000-01-01 00 : 00 : 00.0000000")입니다. 달력 날짜를 넘지 않도록 이동 한 경우 동일한 1 밀리 초 차이 날짜 - 시간은 0 일 (일)에 "DateDiff"를 반환합니다.

2.2.3. 집계 쿼리에서 날짜 - 시간의 Avg 을 먼저 "플로트"로 변환 한 다음 다시 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 "

물론 DateTime Cast 할 수도 있지만 (필요한 경우 DateTime2 로 다시 되돌림), DateTime2DateTime2 의 정확도와 범위 (모두 1753 년 이전)의 이점을 잃을 수 있습니다. 동시에 더하기 / 빼기 / "나이"(대 DateDiff ) / Avg 대한 암시 적 / 쉬운 변환 (일 수)에 대한 암시 적 / 내 경험에 큰 영향을주는 이점을 계산합니다.

Btw, 날짜의 Avg 시간은 중요한 유스 케이스 (또는 적어도 되어야 합니다). a) 기간을 나타 내기 위해 날짜 - 시간 (공통 기본 날짜 - 시간 이후)이 사용되는 평균 지속 시간 (일반적인 관행)을 사용하는 것 외에도, b) 평균 날짜 - 시간이 평균 지속 시간과 비교되는 대시 보드 유형 통계를 얻는 것이 유용합니다. 시간은 범위 / 행 그룹의 날짜 - 시간 열에 있습니다. c) 표준에서 (또는 적어도 표준 이어야 함) ad-hoc 질의 / 열의 값을 더 이상 / 유효하지 않게하거나 더 이상 사용하지 않아야 할 필요가있는 값을 모니터링 / 문제 해결하는 것은 각 값에 대해 발생 수를 나열하는 것입니다 및 해당 값과 관련된 Min , MaxMax 날짜 - 시간 스탬프 (있는 경우)가 표시됩니다.


방금 DATETIME2 대한 또 다른 장점을 adodbapi 습니다. Python adodbapi 모듈의 버그를 피했습니다. 표준 라이브러리 datetime 값이 전달되면 DATETIME 열에 0이 아닌 마이크로 초를 갖지만 열이 다음과 같이 정의되어 있으면 잘 작동합니다. DATETIME2 .


@marc_s 및 @Adam_Poward와 동의합니다 - DateTime2는 앞으로 나아갈 기본 방법입니다. 날짜 범위가 더 넓고 정확도가 높으며 저장량이 같거나 적게 사용됩니다 (정밀도에 따라 다름).

그러나 토론이 놓친 한 가지 ...
@Marc_s states : Both types map to System.DateTime in .NET - no difference there . Both types map to System.DateTime in .NET - no difference there . 이것은 맞지만 역변환은 사실이 아닙니다 ... 날짜 범위 검색을 수행 할 때 중요합니다 (예 : "2010 년 5 월 5 일에 수정 된 모든 레코드 찾기").

Datetime 의 .NET 버전은 DateTime2 와 비슷한 범위와 정밀도 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로 이동할 중요한 이유입니다. 암시 적으로 날짜를 반올림하면 분명히 혼동을 일으킬 수 있습니다.


이 기사 에 따르면 DateTime2를 사용하여 DateTime과 동일한 정밀도를 사용하려면 DateTime2 (3)를 사용해야합니다. 이것은 동일한 정밀도를 제공하고, 더 적은 바이트를 사용하며, 확장 된 범위를 제공해야합니다.


여기에 언급되지 않았고 유용합니다 : 하루에 접미사 추가. 필자는 접미사 논리를 분리하여 날짜가 아닌 원하는 모든 번호에 사용할 수 있도록했습니다.

import time

def num_suffix(n):
    '''
    Returns the suffix for any given int
    '''
    suf = ('th','st', 'nd', 'rd')
    n = abs(n) # wise guy
    tens = int(str(n)[-2:])
    units = n % 10
    if tens > 10 and tens < 20:
        return suf[0] # teens with 'th'
    elif units <= 3:
        return suf[units]
    else:
        return suf[0] # 'th'

def day_suffix(t):
    '''
    Returns the suffix of the given struct_time day
    '''
    return num_suffix(t.tm_mday)

# Examples
print num_suffix(123)
print num_suffix(3431)
print num_suffix(1234)
print ''
print day_suffix(time.strptime("1 Dec 00", "%d %b %y"))
print day_suffix(time.strptime("2 Nov 01", "%d %b %y"))
print day_suffix(time.strptime("3 Oct 02", "%d %b %y"))
print day_suffix(time.strptime("4 Sep 03", "%d %b %y"))
print day_suffix(time.strptime("13 Nov 90", "%d %b %y"))
print day_suffix(time.strptime("14 Oct 10", "%d %b %y"))​​​​​​​






sql sql-server tsql datetime datetime2