[Date] 기준 일자에 특정 기간 후의 날짜(시간 더하기, 빼기=100일 구하기), 두 기간의 날짜 차이 가져오기(오늘은 몇일차?), 1년 365일중 오늘은 몇일째?

MySQL를 사용하다 보면, Date관련 검색 결과를 필요로 하는 경우가 많습니다.

 

쉽게 100일? 만난지 오늘로 몇일? 입사 개월 구하기 등에 유용한 함수들

 

예를 들면,

100일 날짜를 구하기?

우리가 만난 지 오늘은 몇 일차?

입사 개월 수 구하기?

입사 몇 년 차 인지 구하기?

오늘은 1년 365일중에 몇일째?

 

 

날짜에 관련된 정보는 다양하게 변형할 수 있는 조건들이 많고,

사용 빈도도 많은 편이죠.

MySQL의 DATE_ADD/DATE_SUB과 DATEDIFF/TIMESTAMPDIFF함수를 이용해서,

쉽게 날짜 정보를 만들 수 있습니다.

 

DATE_ADD와 DATE_SUB 함수의 Syntax

DATE_ADD(date, INTERVAL value addunit)

##[ addunit ]###
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH

 

DATEDIFF와 TIMESTAMPDIFF함수의 Syntax

TIMESTAMPDIFF(unit, datetime_expr1, datetime_expr2)

##[ unit ]####
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR

TIMESTAMPDIFF()

 

 

 예제 1) 특정 기준일로 오늘은 몇 일차? (참고: 기준일을 포함하려면, 1을 더해야 합니다)

-- 10/12기준으로 오늘(10/14)은 몇일? (기준일을 1일로 포함하여 계산)
SELECT 
    TIMESTAMPDIFF(DAY, '2021-10-10', NOW()) + 1 AS DAYS
    ,NOW() AS TODAY

==[ Result ]=======================
DAYS	TODAY
5	2021-10-14 10:21:11

 

 

예제 2) 오늘 기준으로 100일은? (참고: 오늘이 1일로 계산할 경우 = 기준일 포함)

-- 오늘(10/14) 기준으로 100일은? (기준일을 1일로 계산한다면)
SELECT DATE_ADD('2021-10-14', INTERVAL 99 DAY) AS DAYS_100


##[ Rusult ]####
DAYS_100
2022-01-21

 

 

예제 3) 오늘 기준 입사 몇 개월 되었나? (참고: 입사한 해당 달은 0개월이죠)

-- 한달의 기준은 기준일의 날짜가 되면 한달로 계산됩니다.
SELECT 
    TIMESTAMPDIFF(MONTH, '2021-09-14', '2021-09-30') AS MONTHS
    ,TIMESTAMPDIFF(MONTH, '2021-09-14', '2021-10-13') AS MONTHS1
    ,TIMESTAMPDIFF(MONTH, '2021-09-14', '2021-10-14') AS MONTHS2
    ,TIMESTAMPDIFF(MONTH, '2021-09-14', NOW()) AS MONTHS3
    ,NOW() AS TODAY
    
##[ Result ]####
MONTHS	MONTHS1	MONTHS2	MONTHS3	TODAY
0	0	1	1	2021-10-14 10:47:14

 

 

예제 4) 나의 입사일로 10개월은 언제가 일까? (기준일로 다음 달 그날이 1개월)

-- 오늘 기준으로 10개월은 언제일까?
SELECT 
    DATE_ADD('2020-10-14', INTERVAL 10 MONTH) AS MONTHS
    ,DATE_ADD(NOW(), INTERVAL 10 MONTH) AS MONTHS2
    ,NOW() AS TODAY
   
   
##[ Result ]####
MONTHS		MONTHS2			TODAY
2021-08-14	2022-08-14 11:02:47	2021-10-14 11:02:47

 

 

 

예제 5) 나의 근무 연차는? (참고: 입사하자마자 1년 차로 계산될 때)

-- 년차 계산시, 기준 년도를 1년차로 포함할때.
SELECT 
    TIMESTAMPDIFF(YEAR, '2020-10-14', '2021-10-13') + 1 AS YEARS
    ,TIMESTAMPDIFF(YEAR, '2020-10-14', '2021-10-14') + 1 AS YEARS1
    ,TIMESTAMPDIFF(YEAR, '2020-10-14', NOW()) + 1 AS YEARS2
    ,NOW() AS TODAY
    
##[ Resutl ]####
YEARS	YEARS1	YEARS2	TODAY
1	2	2	2021-10-14 10:51:46

여기서, TIMESTAMPDIFF() + 1을 해줌으로써 입사한 년도부터 1년 계산 값이 나오게 됩니다.

 

 

다음과 같이 일자(Date) 포맷에 계산은 많은 경우에 유용합니다.

많은 리포트 통계 및 특정일자 추출에 DATE_ADD와 TIMESTAMPDIFF는 유용한 함수입니다.

DATE관련 함수들을 잘 이용함으로 프로그램 로직을 줄일 수 있답니다.

 

 

예제 6) 1년 365일중에 오늘은 몇일차 일까요?

//1년 365일중 오늘이 몇일째?
// DAYOFYEAR(NOW())
mysql> SELECT DAYOFYEAR('2022-01-03');
+-------------------------+
| DAYOFYEAR('2022-01-03') |
+-------------------------+
|                       3 | 
+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT DAYOFYEAR('2022-12-31');
+-------------------------+
| DAYOFYEAR('2022-01-03') |
+-------------------------+
|                     365 | 
+-------------------------+
1 row in set (0.00 sec)

DAYOFYEAR()는 1년에 365일중에 오늘은 몇일째? 를 구하는 함수입니다.