데이터베이스 - 날짜 & 시간 함수
MariaDB는 다양한 날짜와 시간과 관련된 함수를 제공한다.
주로 사용되는 함수들을 살펴보자.
now(), curdate()
now() : 현재 날짜와 시간을 알려준다.
select now();위 쿼리는 2023-03-10 14:29:36 로 반환된다.
curdate() : 현재의 날짜를 반환한다.
select curdate();위 쿼리는 2023-03-10을 반환한다.
년/월/일/시간 추출
현재 시간에서 혹은 데이터베이스에 저장된 Date 타입의 데이터에서, 년도만 추출하고 싶거나 월만 추출하는 등 원하는 부분만 추출하여 조회할 수 있다.
- 연도 추출
select year(now());- 월 추출
select month(now*());- 일 추출
select date(now());- 시간
select time(now());time을 사용하는 경우 12:20:00 형태로 반환된다.
여기서 시, 분, 초 단위로 추출하고 싶다면 time대신 각각 hour, minute, second 를 작성하면 된다.
DATE_ADD(), DATE_SUB()
날짜에 연/월/일을 더하거나 뺄 수 있다.
DATE_ADD는 날짜에서 지정한 수 만큼 더한다.
select now() as 현재시간, date_add(now(), interval 1 day) as addDate;
위 코드를 간단하게 풀어보면, 현재날짜(now())에서 하루(1 day)를 더한 데이터가 조회된다.
한눈에 비교할 수 있도록 현재시간도 추가하여 조회한 결과는 아래와 같다.
쿼리에서 하루를 증가시키도록 작성했기 때문에, 현재 날짜와 add_date() 함수를 적용시킨 날짜를 비교해 보면 연도와 월은 같지만 일은 함수에서 지정해준 대로 1이 증가되어 있다.
하루 말고 10일을 증가시키고 싶다면 add_date 함수에서 수치만 조정해주면 된다. == 10 days
일 단위 말고도 월단위/연단위 증가도 모두 가능하다.
월단위 : date_add(now(), interval 1 month)
년단위 : date_add(now(), interval 2 year)
변경할 대상은 현재 날짜 말고도 date 형식이면 모두 올 수 있다.
select date_add('2023-02-01', interval 1 day);
select cu_name, cu_regdate 가입일자, date_add(cu_regdate, interval 1 day) add_date from my_customer;'2023-02-01'이 첫번째 인자로 들어간 경우 조회하면 2023-02-02 가 반환된다.
두번째줄은 테이블에서 유저이름, 가입일자 컬럼과 add_date 함수로 가입일자에서 하루씩 증가시키도록 작성했다.
date_add 함수의 첫번째 인자로 데이터베이스의 데이터타입 컬럼을 넘긴것이다.
두번재줄 쿼리를 돌린 결과다. 기존 가입 날짜와 add_date 컬럼을 비교해 보면 하루씩 증가가 된것을 볼 수 있다.
date_sub는 날짜에서 지정한 수 만큼 빼는 것이다.
select date_sub(now(), interval 1 month);간단한 예시로 현재 날짜에서 한달을 빼도록 작성했다.
결과는 밑의 사진과 같이 나온다
사진을 보면 알 수 있듯이, 더하냐, 빼냐 그 차이일 뿐 date_add 함수와 작성하는 방법은 똑같다.
예시)
유저 테이블에서 가입한지 3개월 이내인 고객의 이름을 조회
select cu_name, cu_regdate from my_customer
where cu_regdate >= DATE_SUB(CURDATE(), INTERVAL 3 MONTH);위 쿼리는 date_sub함수로 현재날짜에서 3개월을 뺀 날짜보다 같거나 큰 가입일자에 해당하는 유저를 반환하는 쿼리다.
현재날짜가 2023년 3월 10일이면, date_sub 함수에서 반환되는 날짜는 2022년 12월 10일이다.
조회조건인 유저의 가입일자가 2022년 12월 10일보다 큰 경우를 조회하는거니까 가입일자가 2022년 12월 10일부터 현재날짜까지 포함된 모든 유저의 이름을 반환한다.
DATEDIFF(), TIMEDIFF()
DATEDIFF : 두 날짜의 차이를 조회한다.
select datediff('2022-10-12', '2022-10-10');두 날짜의 차이는 2일 이므로 쿼리 조회 시 2 라는 결과가 반환된다.
이 때 날짜 차이는 왼쪽을 기준으로 계산하는데, 왼쪽의 날짜가 오른쪽 날짜보다 크다면 양수를 반환하고, 적다면 음수로 반환된다.
select datediff('2022-10-12', '2022-10-14');위 코드는 똑같이 2일 차이지만 2가 아닌 -2 를 반환한다. 왼쪽 날짜가 더 적으므로 음수로 반환되는 것이다.
TIMEDIFF : 두 시간의 차이를 조회한다.
select timediff('10:00:00', '09:00:00');두 시간을 비교하면 조회되는 결과는 01:00:00 이다.
하지만 기준이 되는 앞의 시간이 뒤의 시간보다 늦다면 결과는 날짜를 비교하는 DATEDIFF() 함수와는 다른 형식으로 조회된다.
select timediff('10:00:00', '11:00:00');왼쪽 시간이 오른쪽 시간보다 1시간이 늦다. 예상했던 조회결과는 -01:00:00 이었지만, 실제로 조회된 결과는 23:00:00 이다.
반환되는 값의 형식은 'HH:MM:SS' 인데, 이때 시간 값이 음수일 경우에 음수 부호 대신 하루를 나타내는 시간인 24를 더하여 양수 값으로 반환한다. 그래서 -1 에서 24를 더한 23:00:00 이 반환된다.
DATE_FORMAT()
DATE_FORMAT()은 날짜와 시간을 원하는 형식으로 지정할 수 있다.
DATE_FORMAT() 함수 밑의 밑의 구문과 같이 사용된다.
DATE_FORMAT(date, format);
date는 날짜 형식(date, timestamp, datetime)의 데이터이며, format은 변환하고자 하는 날짜 형식을 지정하는 문자열이다.
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');위 쿼리의 결과는 2023-03-10 10:30:00 로 현재 날짜를 지정한 형식으로 반환한다.
<날짜 FORMAT 코드>
%Y : 4자리 연도 (예: 2023)
%y : 2자리 연도 (예: 23)
%m : 월 (01부터 12까지)
%c : 월 (1부터 12까지)
%d : 일 (01부터 31까지)
%e : 일 (1부터 31까지)
%H : 24시간 형식으로 표시한 시 (00부터 23까지)
%h : 12시간 형식으로 표시한 시 (01부터 12까지)
%i : 분 (00부터 59까지)
%s : 초 (00부터 59까지)
%p : AM 또는 PM (대문자)
<구분자를 사용한 날짜 FORMAT 형식>
- : 하이픈(-)
/ : 슬래시(/)
. : 마침표(.)
: : 콜론(:)
: 공백
예시)
SELECT DATE_FORMAT('2023-03-10', '%d-%m-%Y');위 코드는 '2023-03-10' 을, 오른쪽의 날짜 포맷 형식에 맞춰서 반환될 것이다.
'2023-03-10' 은 '%Y-%m-%d' 형식으로 되어있는데, 오른쪽의 날짜 포맷형식으로 바꾸면 조회되는 데이터는 '10-03-2023' 이다.
SELECT DATE_FORMAT('2023-03-10 14:30:45', '%Y년 %c월 %e일 %p %h시 %i분 %s초');그럼 위의 쿼리는 어떻게 변경되어 나타날까?
구분자를 이용하여 날짜와 시간을 구분하는 대신 년,월,일,시,분,초로 형태를 지정하였으므로 조회되는 값은 아래와 같다.
2023년 3월 10일 오후 2시 30분 45초 와 같은 형식으로 조회될 것이다.
