[SQL이론] DATE 형태 변형, 일자 변경

DATE 형태 변형

 

MY SQL에서 자주보는 날짜 형식인 DATETIME타입은 YYYY-MM-DD hh:mm:ss 형식으로 나타난다. 

 

 

초단위까지 있는 날짜를 BETWEEN 등으로 구간으로 설정한다면 

DATETIME 그대로 사용하는데 큰 문제가 없는 경우도 있으나, 

문제에서 연/월/일 까지만 출력하라고 요청할 때가 있다. 

이럴때, DATE 또는 DATE_FORMAT('날짜컬럼','형식)을 입력해서 이를 수정할 수 있다. 

 

DATE 타입은 YYYY-MM-DD의 형식을 가진다. 

 

만일 DAY라는 컬럼의 값이 '2023-08-08 01:48:10'라고 가정할때, 

DATE(DAY) 는 '2023-08-08'이 된다. 

 

만일, 08-AUG-23으로 표현하거나, 다른 형식의 모양으로 연월일을 표시하고 싶다면

DATE_FORMAT()함수를 활용해야한다. 

 

DATE_FORMAT('날짜컬럼', '형식')의 구성인데, 형식은 아래의 표를 참고하여 내가 원하는 형식으로 만들 수 있다. 

-- 날짜컬럼과 형식 사이에는 꼭!! 쉼표가 들어가야한다. 쉼표를 잊고서 나온 오류가 몇번째인지... 

 

예를들어 DAY의 값을 영국식 표기 처럼 'AUG-08-23'으로 표기하고 싶다면

SELECT DATE_FORMAT(DAY, '%b-%d-%y)
FROM DT

위와같이 입력을 하면 내가 원하는 형식으로 연월일을 구성할 수 있다. 

 

SpecifierDescription

%a	Abbreviated weekday name (Sun..Sat)
%b	Abbreviated month name (Jan..Dec)
%c	Month, numeric (0..12)
%D	Day of the month with English suffix (0th, 1st, 2nd, 3rd, …)
%d	Day of the month, numeric (00..31)
%e	Day of the month, numeric (0..31)
%f	Microseconds (000000..999999)
%H	Hour (00..23)
%h	Hour (01..12)
%I	Hour (01..12)
%i	Minutes, numeric (00..59)
%j	Day of year (001..366)
%k	Hour (0..23)
%l	Hour (1..12)
%M	Month name (January..December)
%m	Month, numeric (00..12)
%p	AM or PM
%r	Time, 12-hour (hh:mm:ss followed by AM or PM)
%S	Seconds (00..59)
%s	Seconds (00..59)
%T	Time, 24-hour (hh:mm:ss)
%U	Week (00..53), where Sunday is the first day of the week; WEEK() mode 0
%u	Week (00..53), where Monday is the first day of the week; WEEK() mode 1
%V	Week (01..53), where Sunday is the first day of the week; WEEK() mode 2; used with %X
%v	Week (01..53), where Monday is the first day of the week; WEEK() mode 3; used with %x
%W	Weekday name (Sunday..Saturday)
%w	Day of the week (0=Sunday..6=Saturday)
%X	Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%x	Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%Y	Year, numeric, four digits
%y	Year, numeric (two digits)
%%	A literal % character
%x	x, for any “x” not listed above

날짜 함수의 형식에 대해서는 아래 링크를 참조하면 더 많은 내용들을 확인할 수 있다.

MySQL :: MySQL 8.0 Reference Manual :: 12.7 Date and Time Functions

 

DATE 날짜 조정

SQL문제를 풀다보면, 조건을 지정해야하는데

그 조건이 주어진 날의 앞 뒷날을 포함해야하는 등

주어진 날짜에서 며칠을 이동시켜야하는 경우가 종종 발생한다. 

 

예를들어, 아래와 같은 테이블이 있다고 가정을 하고, 아래의 결과값처럼 MOVING AVG(이동평균)을 구하는 문제를 마주한다면 우리는 어떻게 해야하겠는가

DAY	TEMP	LOCATION
2023-08-01	35	SEOUL
2023-08-02	36	SEOUL
2023-08-03	34	SEOUL
2024-08-04	37	SEOUL

결과값

DAY	TEMP	MOVING_AVG	LOCATION
2023-08-01	35	(35+36)/2	SEOUL
2023-08-02	36	(35+36+34)/3	SEOUL
2023-08-03	34	(36+34+47)/3	SEOUL
2024-08-04	37	(34+37+24)/3	SEOUL

 

우리는 현재 주어진 DATE값에서 하루 전날부터 다음날까지의 TEMP를 더하고 그 값의 평균을 출력하는 값을 만들어내야한다.

 

SELECT DAY
     , TEMP
     , (SELECT AVG(S2.TEMP)
        FROM seoul_temp S2 
        WHERE S2.DATE 
          BETWEEN DATE_SUB(S1.DAY, INTERVAL 1 DAY) 
          AND DATE_ADD(S1.DAY, INTERVAL 1 DAY)
        )AS MOVING_AVG
FROM seoul_temp S1
ORDER BY DAY

앞서 사용한 것처럼, 날짜를 변경하기 위해선 DATE_SUB(날짜, INTERVAL 변경하고 싶은 연/월/일/시/분/초)를 입력한다. 

중요한건, 날짜 컬럼 뒤! 콤마!!