[MySQL] 날짜 / 시간 / 통계 조회

unknownomad 2022. 1. 11. 22:33

날짜 / 시간 표현

#현재 시간(기본) : 2022-01-01 12:00:00
SELECT NOW();

#날짜 ONLY : 2022-01-01
SELECT DATE(NOW());

#시간 ONLY : 12:00:00
SELECT TIME(NOW());

#년도 ONLY : 2022
SELECT YEAR(NOW());

#월 ONLY : 1
SELECT MONTH(NOW());

#날짜 포맷 지정 : 2022/01/01
SELECT DATE_FORMAT(NOW(), '%Y/%m/%d');

#시간 포맷 지정 : 12 00 00
SELECT DATE_FORMAT(NOW(), '%H %i %s);

날짜 더하기 / 빼기

DATE_ADD	DATE_SUB
DATE_ADD(기준 날짜, INTERVAL)	DATE_SUB(기준 날짜, INTERVAL)

1. 날짜 더하기 : DATE_ADD

#DATE_ADD 사용법
DATE_ADD(기준 날짜, INTERVAL)

#DATE_ADD 예제
#현재 시간
2022-01-01 12:00:00

#현재 시간에서 1초 더하기 : 2022-01-01 12:00:01
SELECT DATE_ADD(NOW(), INTERVAL 1 SECOND);

#현재 시간에서 1분 더하기 : 2022-01-01 12:01:00
SELECT DATE_ADD(NOW(), INTERVAL 1 MINUTE);

#현재 시간에서 1시간 더하기 : 2022-01-01 13:00:00
SELECT DATE_ADD(NOW(), INTERVAL 1 HOUR);

#현재 시간에서 1일 더하기 : 2022-01-02 12:00:00
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY);

#현재 시간에서 1개월 더하기 : 2022-02-01 12:00:00
SELECT DATE_ADD(NOW(), INTERVAL 1 MONTH);

#현재 시간에서 1년 더하기 : 2023-01-01 12:00:00
SELECT DATE_ADD(NOW(), INTERVAL 1 YEAR);

#현재 시간에서 1년 빼기 : 2021-01-01 12:00:00
SELECT DATE_ADD(NOW(), INTERVAL -1 YEAR);

2. 날짜 빼기 : DATE_SUB

#DATE_SUB 사용법
DATE_SUB(기준 날짜, INTERVAL)

#DATE_SUB 예제
#현재 시간
2022-01-01 12:00:00

#현재 시간에서 1초 빼기 : 2022-01-01 11:59:59
SELECT DATE_SUB(NOW(), INTERVAL 1 SECOND);

#현재 시간에서 1분 빼기 : 2022-01-01 11:59:00
SELECT DATE_SUB(NOW(), INTERVAL 1 MINUTE);

#현재 시간에서 1시간 빼기 : 2022-01-01 11:00:00
SELECT DATE_SUB(NOW(), INTERVAL 1 HOUR);

#현재 시간에서 1일 빼기 : 2021-12-31 12:00:00
SELECT DATE_SUB(NOW(), INTERVAL 1 DAY);

#현재 시간에서 1개월 빼기 : 2021-12-01 12:00:00
SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH);

#현재 시간에서 1년 빼기 : 2021-01-01 12:00:00
SELECT DATE_SUB(NOW(), INTERVAL 1 YEAR);

#현재 시간에서 1년 더하기 : 2023-01-01 12:00:00
SELECT DATE_SUB(NOW(), INTERVAL -1 YEAR);

조건 별 조회

#최근 하루
SELECT *
FROM TABLE_NAME
WHERE REG_DATE BETWEEN DATE_ADD(NOW(), INTERVAL -1 DAY) AND NOW();

#최근 일주일
SELECT *
FROM TABLE_NAME
WHERE REG_DATE BETWEEN DATE_ADD(NOW(), INTERVAL -1 WEEK) AND NOW();

#최근 한달
SELECT *
FROM TABLE_NAME
WHERE REG_DATE BETWEEN DATE_ADD(NOW(), INTERVAL -1 MONTH) AND NOW();

#특정 날짜 지정 방법1(2022년 2월 8일)
SELECT *
FROM TABLE_NAME
WHERE '2022-02-08 00:00:00' <= REG_DATE AND REG_DATE <= '2022-02-09 00:00:00';

#특정 날짜 지정 방법2(2022년 2월 8일)
SELECT *
FROM TABLE_NAME
WHERE DATE_FORMAT(REG_DATE, '%Y-%m-%d') = '2022-02-08';

#날짜 조건
#방법1
SELECT *
FROM TABLE_NAME
WHERE DATE(REG_DATE) BETWEEN '2022-01-01' AND '2022-12-31';
#방법2
SELECT *
FROM TABLE_NAME
WHERE '2022-01-01' <= DATE(REG_DATE) AND DATE(REG_DATE) <= '2022-12-31';

#월별 조건
SELECT *
FROM TABLE_NAME
WHERE DATE_FORMAT(REG_DATE, '%Y-%m') BETWEEN '2022-01' AND '2022-03';

통계 조회

1. 일별 통계

#일별 통계
SELECT DATE(REG_DATE) AS DATE
    , SUM(CNT)
FROM TABLE_NAME
GROUP BY DATE;

2. 주간 통계

#주간 통계
SELECT DATE_FORMAT(DATE_SUB(REG_DATE, INTERVAL (DAYOFWEEK(REG_DATE)-1) DAY), '%Y/%m/%d') AS START
    , DATE_FORMAT(DATE_SUB(REG_DATE, INTERVAL (DAYOFWEEK(REG_DATE)-7) DAY), '%Y/%m/%d') AS END
    , DATE_FORMAT(REG_DATE, '%Y%U') AS DATE
    SUM(CNT)
FROM TABLE_NAME
GROUP BY DATE;

3. 월간 통계

#월간 통계
SELECT MONTH(REG_DATE) AS DATE
    , SUM(CNT)
FROM TABLE_NAME
GROUP BY DATE;

4. 기간 별 통계

#기간 별 통계
SELECT DATE(REG_DATE) AS DATE
    , SUM(CNT)
FROM TABLE_NAME
WHERE STR_TO_DATE('2017-04-01', '%Y-%m-%d') <= DATE(REG_DATE)
AND DATE(REG_DATE) <= STR_TO_DATE('2017-04-10', '%Y-%m-%d')
GROUP BY DATE;

DAYOFWEEK / DAYNAME

DAYOFWEEK(DATE)	DAYNAME(DATE)
DATE의 요일을 숫자로 리턴	DATE의 요일 이름을 리턴
1	일요일
2	월요일
3	화요일
4	수요일
5	목요일
6	금요일
7	토요일

DATE_FORMAT

1. 오늘 날짜 / 시간을 가져오는 함수

CURDATE()	CURTIME()	NOW()	SYSDATE()
현재 날짜만 가져오기	현재 시간만 가져오기	현재 날짜 + 시간 가져오기	서버 시간 가져오기
2022-02-08	12:30:30	2022-02-08 12:30:30	2022-02-08 12:30:30
쿼리가 실행되는 시간 기준			SYSDATE() 함수가 실행되는 시간 기준

* 느린 쿼리 사용 시 각 함수마다 시간이 달라질 수 있으므로,

여러 컬럼에 같은 시간이 들어가야 할 때는 SYSDATE()보단 NOW()를 쓰는 게 더 나음

2. 날짜 형식 변환

#날짜 / 시간 형식 변환
DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s')
#결과
2022-02-08 12:30:30

%Y	연	4자리 연도
%y	연	2자리 연도
%m	월	2자리 (00-12)
%c	월	1자리, 10보다 작을경우 (1-12)
%M	월	이름(January, February…)
%b	월	줄인 이름(Jan, Feb…)
%d	일	2자리 (00-31)
%e	일	1자리, 10보다 작을경우 (0-31)
%D	일	1st, 2nd…
%H	시	24시간 형식 (00-23)
%h	시	12시간 형식 (01-12)
%I	시	12시간 형식 (01-13)
%k	시	24시간 형식, 10보다 작을경우 한자리 (0-23)
%l	시	12시간 형식, 10보다 작을경우 한자리 (1-12)
%i	분	2자리 (00-59)
%S	초	2자리 (00-59)
%s	초	2자리 (00-59)
%f	마이크로초	100만분의 1초
%p	오전/오후	AM/PM
%T	시분초	24시간 형식 (hh:mm:ss)
%r	시분초 오전/오후	12시간 형식 (hh:mm:ss AM/PM)
%j	일	그해의 몇번째 일인지 표시 (001-366)
%w	일	그주의 몇번째 일인지 표시 (0=일요일, 6=토요일)
%W	주	이름(Monday,Tuesday…)
%a	주	줄인 이름(Mon,Tue…)
%U	주	그해의 몇번째 주인지 표시 (00-53) 일요일이 주의 첫번째일
%u	주	그해의 몇번째 주인지 표시 (00-54) 월요일이 주의 첫번째일
%X	연	그주가 시작된 해을 표시, %V와 같이 사용
%x	연	그주가 시작된 해을 표시, %v와 같이 사용
%V	주	그주가 시작된 해의 몇번째 주인지 표시 (01-53) 일요일이 주의 첫번째일 %X 와 함께사용
%v	주	그주가 시작된 해의 몇번째 주인지 표시 (01-53) 월요일이 주의 첫번째일 %x 와 함께사용

출처

https://easy-coding.tistory.com/110

https://extbrain.tistory.com/58

https://bluexmas.tistory.com/626

https://codingdog.tistory.com/entry/mysql-dayofweek-dayname-%ED%95%A8%EC%88%98-%EC%9A%94%EC%9D%BC%EC%9D%84-%EB%8F%8C%EB%A0%A4%EC%A4%80%EB%8B%A4

https://m.blog.naver.com/PostView.naver?isHttpsRedirect=true&blogId=callsonda&logNo=220363810001

http://www.w3schools.com/sql/func_date_format.asp