3. 날짜형 함수
날짜형 데이터 타입: YEAR, DATE, TIME, DATETIME, TIMESTAMP
- 숫자형: YYYYMMDDHHMMSS.uuuuuu
- 문자형: 'YYYY-MM-DD HH:MM:SS.uuuuuu'
공간 절약과 산술 연산을 위해 내부적으로는 숫자형으로 저장한다.
하지만 출력 시에는 맥락에 따라 문자형/숫자형으로 출력한다.
-> 디폴트는 문자형이다.
SYSDATE(), NOW() 함수
- SYSDATE(): 현재 시간
- NOW(): 명령어가 실행된 시간, 새로운 튜플이 실행되는 시간
SELECT NOW(); /* '2020-05-15 14:41:46' */
SELECT NOW() + 0; /* 20200515144146 */일반적으로 문자형으로 출력하지만, + 0(정수)를 해줌으로써 맥락을 숫자형으로 바꿨다. 이런 경우 숫자형으로 출력된다.
CREATE TABLE tmp (
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(255) NOT NULL,
created_on DATETIME NOT NULL DEFAULT NOW()
/* or CURRENT_TIMESTAMP */
);NOW() 사용 시, 새로운 투플이 입력(INSERT문) 될 때마다 created_on에 입력된 시간이 기록된다.
Datetime Dimension 관련 함수
날짜와 시간의 각 요소를 출력하는 함수이다.
SELECT TIMESTAMP(NOW()) AS CurrentTimestamp,
DATE(NOW()) AS CurrentDate,
YEAR(NOW()) AS Year,
MONTH(NOW()) AS Month,
DAY(NOW()) AS Day,
MONTHNAME(NOW()) AS MonthName,
DAYNAME(NOW()) AS DayName,
WEEKDAY(NOW()) AS WeekIndex;
/* Monday 0, Tuesday 1, … Sunday 6 */
TIME(NOW()) AS CurrentTime,
HOUR(NOW()) AS Hour,
MINUTE(NOW()) AS Minute,
SECOND(NOW()) AS Second;
이렇게 쪼개기가 가능하다.
✋ 여기서 잠깐 ! TIMESTAMP(NOW()), DATE(NOW()), TIME(NOW())의 데이터 타입을 알 수 있을까?
⭐ 정답은 No 다.
날짜형 데이터 타입: YEAR, DATE, TIME, DATETIME, TIMESTAMP는 맥락에 따라 문자형/숫자형으로 출력하기 때문에 출력물로는 알 수 없다.
return값의 데이터 타입을 파악하기 위해서는 코드를 확인해봐야한다. + 0 이 없으니 디폴트인 문자형으로 출력했다고 볼 수 있다.
EXTRACT() 함수
특정 unit만 출력한다.
Format
EXTRACT(unit FROM date)Example
DATEDIFF(end, begin) 함수
두개의 DATE/DATETIME/TIMESTAMP 값의 차이를 계산한다.
- 인자가 DATETIME/TIMESTAMP 인 경우 시간 부분은 무시하고 날짜만 처리한다.
- 두 인자의 타입이 다르면 NULL 값을 리턴한다.
SELECT DATEDIFF('2009-03-01', '2009-01-01') diff; /* 59*/⭐ 원래 문자열인데 DATEDIFF 함수를 사용하면 숫자로 판단해서 계산해준다.
TIMEDIFF(end, begin) 함수
두개의 TIME/DATETIME 값의 차이를 계산한다.
- 가능한 범위: -838:59:59 ~ 838:59:59
Example
SELECT TIMEDIFF('2009-02-01 00:00:00', '2009-01-01 00:00:00') diff
/* 744:00:00 */
SELECT TIMEDIFF('2009-03-01 00:00:00', '2009-01-01 00:00:00') diff;
SHOW WARNINGS: /* 838:59:59, 범위 에러 */
TIMESTAMPDIFF(unit, begin, end) 함수
Format
TIMESTAMPDIFF(unit, begin, end);Example
SELECT TIMESTAMPDIFF(MINUTE, '2010-01-01 10:00:00',
'2010-01-01 10:45:59') diff; /* 45 */분 단위로 보겠다는 의미이다.
TIMEDIFF(t1, t2) = t1 - t2 이고,
TIMESTAMPDIFF(unit, t1, t2) = t2 - t1 이다. -> 순서 주의하자 !
만나이를 계산해라.
SELECT PLAYER_NAME AS 선수명, BIRTH_DATE AS 생일,
TIMESTAMPDIFF(YEAR, BIRTH_DATE, DATE(NOW())) AS 나이
FROM PLAYER;TIMESTAMPDIFF()의 경우, 기준 unit을 완전히 지나야만 값이 1 증가한다. 그래서 unit을 사용해준다.
위 경우에는, YEAR 단위이므로, 365일을 지나야만 1이 증가한다.
DATE_ADD(), DATE_SUB() 함수
DATE/DATETIME 값에 interval을 더하거나 뺀다.
Format
DATE_ADD(start_date, interval_expression);DATE_SUB(start_date, interval_expression);Example
SELECT DATE_ADD('2000-01-01', INTERVAL 12 HOUR) result;
/* 2000-01-01 12:00:00 */여기서는 DATE에서 DATETIME으로 자동 타입 변환이 일어난다.
TIME_ADD(), TIME_SUB() 함수
위 개념과 동일하다.
DATE_FORMAT() 함수
날짜 타입인데, 출력은 문자형으로 출력해서 타입 구분에 주의해야한다.
Format
DATE_FORMAT(date, format);
☞ format := format_specifier 들의 조합
☞ format_specifier := %Y|%y|%M|%m|%D|%d|%H|%h|%i|%i|%S|%s|%p
/* '분' 주의 */Example
SELECT PLAYER_NAME,
DATE_FORMAT(BIRTH_DATE, '%Y-%m-%d'),
DATE_FORMAT(BIRTH_DATE, '%D %M %Y')
FROM PLAYER;
예를 들어, BIRTH_DATE가 2001-02-28로 출력됐고 (문자형 타입), DATE_FORMAT(BIRTH_DATE)가 2001-02-28로 출력됐으면 (날짜형 타입), 출력물만 봐서는 구분할 수 없다.
< format_specifier >
STR_TO_DATE() 함수
문자열을 입력받아서 날짜형 타입으로 변환하여 출력한다.
Format
STR_TO_DATE(string, format);여기서 format은 출력 포맷을 지정하는게 아니라, 입력 문자열을 어떻게 해석할 것인지를 나타낸다.
Example
SELECT STR_TO_DATE('21,5,2013', '%d,%m,%Y'); /* 2013-05-21 */
SELECT STR_TO_DATE('2013', '%Y'); /* 2013-00-00 */
SELECT STR_TO_DATE('113005', '%h%i%s'); /* 11:30:05 */
SELECT STR_TO_DATE('11', '%h'); /* 11:00:00 */
SELECT STR_TO_DATE('20130101 1130', '%Y%m%d %h%i');
/* 2013-01-01 11:30:00*/
SELECT STR_TO_DATE('21,5,2013 extra characters', '%d,%m,%Y');
/* 2013-05-21 */