[SQL/MySQL] DATE 관련 함수들

---

🟢 DATE 형 다루기

▫️  날짜를 문자형 형태로 비교하기 위해 DATE 타입 -> STRING 타입으로 변환

  ▫️ DATE_FORMAT( date ,  'FORMAT' )  :  DATE 형 ➡️  STRING 형

     - DATE_FORMAT(PUBLISHED_DATE, '%Y') : 데이터에서 연도 부분만 빼서 출력 >> 2021-10-24 = '2021' 출력

     - 기본적으로 DATE - STRING 형 변환을 거치면 시간/분/초 까지 함께 변환된다 (가공 필요)

    🔻 FORMAT 종류

'FORMAT'	설명
%Y	연도 4자리 모두
%y	연도 뒤 2자리 ( 2024 => 24 )
%a	요일 (줄임 버전 >>>  Mon ...)
%W	요일 (풀 버전 >>> Monday, ...)
%w	요일 (숫자 버전  >>>  0[Sunday] ~ 6 [Saturday])
%b	월 (줄임 버전)
%M	월 (풀 버전)
%c	월 (숫자 버전 & 자릿수 고정 X >>>   0 [Jan] ~ 12 [Dec])
%m	월 (숫자 버전 & 자릿수 2자리 고정 O >>>  00 [Jan] ~ 12 [Dec])
%D	일 (접미사 버전 >>> 0th, 1st, 2nd, ... )
%d	일 (숫자, 한 달 단위 >>> 0, 1, 2, 3, ..., 31)
%j	일 (숫자, 1년 단위 >>> 001, 002, 003, ...)
%p	오전 / 오후 구분자 (AM / PM) 만 출력
%T	24시간 기준 시간만 표기 ( 15:41:52 )
%r	12시간 기준 시간 표기 & 오전/오후 구분 (03:41:52 PM)
%f	Micro sec 표기 (000000 ~ 999999)
%H	시간 (24시간 >>> 00 ~ 23)
%k	시간 (24시간 >>> 00 ~ 23)
%h	시간 (12시간 >>> 01 ~ 12)
%I(대문자 i)	시간 (12시간 >>> 01 ~ 12)
%i	분 (1시간 단위 >>> 00 ~ 59)
%S, %s	초 (00 ~ 59)
%U	주 (1년 단위, 일주일 시작 요일 = 일요일 기준 >>> 00 ~ 53)
%u	주 (1년 단위, 일주일 시작 요일 =  월요일 기준 >>> 00 ~ 53)
%v	주 (1년 단위, 일주일 시작 요일 = 일요일 기준 >>> 01 ~ 54)

 

▫️ STR_TO_DATE( str, format)  :  STRING 형 ➡️  DATE 형

    - 형식 문자열에 날짜 및 시간 부분이 모두 포함된 경우 DATETIME 값 반환

    - STR_TO_DATE('20230118', '%Y%m%d' ) : 20230118 를 ' %Y%m%d ' 형식 DATE 로 변환 => 2023-01-18

    - STR_TO_DATE('20230118', '%Y' )  : 날짜의 연도 반환 => 2023

 ▫️ 특정 기간 내 데이터 조회 : BETWEEN  ~ AND ~

 ❗❗❗ 이때, end 조건은 exclusive 하므로 +1 해서 범위로 잡을 것!!!    아닌듯?;;;

SELECT * FROM TABLE
WHERE time BETWEEN '2022-10-01' AND '2022-10-30' -- 2022년 10월 데이터만 조회
WHERE time NOT BETWEEN '2022-10-01' AND '2022-10-30' -- 2022년 10월 데이터만 **제외**하고 조회

   

- 특정 날짜와 일치하는 데이터 조회 : WHERE time = '2022-10-20'

- 현재 날짜에서 한달 전 데이터 조회 : WHERE time < DATE.ADD(NOW(), INTERVAL -30 DAY)

🟢 날짜, 시간 계산 함수

 ▫️ DATE_ADD( 기준날짜, INTERVAL ) : 날짜에서 시간 더하기

     INTERVAL : INTERVAL 1 SECOND/MINUTE/HOUR/DAY/MONTH/YEAR (음수도 가능)

 ▫️ DATE_SUB( 기준날짜, INTERVAL ) : 날짜에서 시간 빼기

 ▫️ DATEDIFF( 종료일, 시작일 ) : 두 기간 사이의 일수 계산

     - 날짜 포맷에 시간 포함되어도 시간은 포함 X

     - 날짜 범위에서 벗어나는 값 => NULL 반환

 ▫️ TIMEDIFF( 종료 시간, 시작 시간 ) : 두 시간 사이의 시간 계산

     - 반환 포맷 : HH : MM : SS

     - 시간 범위에서 벗어나는 값 => NULL 반환

 ▫️ PEROID_DIFF( 종료 년월, 시작 년월 ) : 두 기간 사이의 개월 수 차이 계산

     - 년월 형식 : YYYYMM 또는 YYMM 형식

     - 정수 형식으로 반환

▫️ TIMESTAMPDIFF( UNIT, 시작일, 종료일 ) : 여러 형태의 계산 가능

     - UNIT : 반환 값 형식 => MONTH, YEAR, HOUR

     - UNIT값에 따라 다른 값 반환 (MONTH면 개월 수 차이 계산)

🟢 만약, 데이터와 관계없이 1 ~ 12까지 모든 기간의 날짜를 출력하고 싶다면 

임시로 1 ~ 12까지 데이터를 모두 가진 테이블을 생성한 뒤, JOIN 해줘야 함!

 ▫️ WITH RECURSIVE 를 활용해 2022-01-01 ~ 2022-12-31 까지 데이터 테이블 생성

WITH RECURSIVE TIME AS (SELECT DATE_FORMAT('20220101', '%Y-%m-%d') AS START
                        UNION ALL
                        SELECT DATE_ADD(START, INTERVAL 1 DAY)
                        FROM TIME
                        WHERE START < DATE_FORMAT('20221231', '%Y-%m-%d'))

 

 

출처

• https://heytech.tistory.com/50
• https://horang98.tistory.com/10
• https://kigo23.tistory.com/13