[mssql] 시계열 데이터 연산 함수(DATEADD/DATEDIFF)

※ 참고 링크

※ SSMS에서 사용하는 함수를 드래그한 후 Shift +  F1을 누르면 함수 공식 Document 홈페이지로 이동한다.

 

 

1. DATEADD

- DATEADD 함수의 기본적인 형태이다.

- datepart를 기준으로 number만큼 date를 더하거나 빼주는 함수이다.

DATEADD (datepart , number , date )  

  -  datepart : 연산할 데이터의 타입

  -  number  : 연산할 숫자

  -  date : 연산할 데이터

-- 음수도 가능하다
 
DECLARE @datetime2 datetime2;  
SET @datetime2 = '2007-01-01 01:01:01.1111111';  
        --Statement                               Result     
-------------------------------------------------------------------   
SELECT DATEADD(quarter,4,@datetime2) --2008-01-01 01:01:01.1111111  
,DATEADD(month,13,@datetime2)        --2008-02-01 01:01:01.1111111  
,DATEADD(dayofyear,365,@datetime2)   --2008-01-01 01:01:01.1111111  
,DATEADD(day,365,@datetime2)         --2008-01-01 01:01:01.1111111  
,DATEADD(week,5,@datetime2)          --2007-02-05 01:01:01.1111111  
,DATEADD(weekday,31,@datetime2)      --2007-02-01 01:01:01.1111111  
,DATEADD(hour,23,@datetime2)         --2007-01-02 00:01:01.1111111  
,DATEADD(minute,59,@datetime2)       --2007-01-01 02:00:01.1111111  
,DATEADD(second,59,@datetime2)       --2007-01-01 01:02:00.1111111  
,DATEADD(millisecond,1,@datetime2)   --2007-01-01 01:01:01.1121111  

1.1 GETDATE()를 활용하여 현재날짜 기준으로 연산하기

SELECT GETDATE() AS Today,
DATEADD( day, 10, GETDATE()) AS After_10days,
DATEADD( day, -10, GETDATE()) AS ten_days_ago
 
---------------------------------------------------------------------------
         Today            |      After_10days       |       ten_days_ago
2021-02-10 12:11:16.333    | 2021-02-20 12:11:16.333 | 2021-01-31 12:23:07.627

1.2 

 

2. DATEDIFF

- DATEDIFF 함수의 기본적인 형태이다.

- datepart를 기준으로 number만큼 date를 더하거나 빼주는 함수이다.

DATEDIFF ( datepart , startdate , enddate )  

  -  datepart : 연산할 데이터의 타입

  -  number  : 연산할 숫자

  -  date : 연산할 데이터

SELECT 
    DATEDIFF(year,        '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'),
    DATEDIFF(quarter,     '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'),
    DATEDIFF(month,       '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'),
    DATEDIFF(dayofyear,   '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'),
    DATEDIFF(day,         '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'),
    DATEDIFF(week,        '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'),
    DATEDIFF(hour,        '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'),
    DATEDIFF(minute,      '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'),
    DATEDIFF(second,      '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'),
    DATEDIFF(millisecond, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'),
    DATEDIFF(microsecond, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'),
    DATEDIFF(HOUR, '2020-11-11 12:12:12.9999999', '2020-11-12 00:00:00.0000000')

2.1 다른 타입으로 테스트 해보기

  - 가능

-- 하이픈 제거
SELECT DATEDIFF(HOUR, '20201111 12:12:12.9999999', '20201112 00:00:00.0000000')
-- HHMMSS만 (HHMMSS만 있기 때문에 year, month 등은 0으로 출력됨)
SELECT DATEDIFF(HOUR, '12:12:12.9999999', '00:00:00.0000000')
-- YYYYmmdd만
SELECT DATEDIFF(HOUR, '20200101', '20200102')
-- YYYYmmdd에 스타일 다르게
SELECT DATEDIFF(HOUR, '2020-01-01', '20200102')
SELECT DATEDIFF(HOUR, '2020-01-01', '2020/01/02')
SELECT DATEDIFF(HOUR, '2020-01-01', '01/02/2020')
SELECT DATEDIFF(HOUR, '20200101', '01/02/2020')
SELECT DATEDIFF(HOUR, '2020.01.01', '01/04/2020') -- 01/04/2020 이런 경우에는 월일년 순으로 인식한다.
SELECT DATEDIFF(HOUR, '2020 01 01', '01/03/2020')
 
-- YYYYmmddHHMMSSf 스타일 다르게
SELECT DATEDIFF(HOUR, '20201111 12:12:12.9999999', '2020-11-12 00:00:00.0000000')

  - Error

    - YYYYmmddHHMMSS가 구분되어 있지 않는 String Type의 경우 Error가 발생

SELECT DATEDIFF(HOUR, '202011111212129999999', '202011120000000000000')
SELECT DATEDIFF(HOUR, '20201111 1212129999999', '20201112 0000000000000')

 

3. DATEDIFF, DATEADD 응용

- YYYYmmdd형태로 적재되어 있는 AppDate라는 컬럼에서 현재시간 기준으로 지난 일주일 데이터만 필터링하고 싶은 경우

WHERE
AppDate BETWEEN CONVERT(nvarchar(8), DATEADD(week, DATEDIFF(week, 7, GETDATE()), 0), 112)
    AND CONVERT(nvarchar(8), DATEADD(week, DATEDIFF(week, 7, GETDATE()), 6), 112)