- DATE: 'YYYY-MM-DD' Format
- TIME: 'HH:MM:SS' Format
- DATETIME: 'YYYY-MM-DD HH:MM:SS' Format
CURDATE() = Current Date
CURTIME() = Current Time
NOW() = Current Date and Time
SELECT CURDATE();
SELECT CURTIME();
SELECT NOW();
- DATE: 'YYYY-MM-DD' Format
SELECT name, birthdt, YEAR(birthdt) FROM people;
SELECT name, birthdt, MONTH(birthdt) FROM people;
SELECT name, birthdt, DAY(birthdt) FROM people;
SELECT name, birthtime, HOUR(birthtime) FROM people;
SELECT name, birthtime, MINUTE(birthtime) FROM people;
SELECT name, birthtime, SECOND(birthtime) FROM people;
% 사용하기
(참고: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format)
SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W-%M-%Y');
예시. bithdate 와 현재와의 차이 출력 (날짜 일 차이 출력)
SELECT name, birthdate, DATEDIFF(NOW(), birthdate) FROM people;
예시. birthdt 에서 1개월 가산하여 출력
SELECT birthdt, DATE_ADD(birthdt, INTERVAL 1 MONTH) FROM people;
SELECT birthdt, birthdt + INTERVAL 1 MONTH FROM people;
예시. birthdt 에서 10초 가산하여 출력
SELECT birthdt, DATE_ADD(birthdt, INTERVAL 10 SECOND) FROM people;
예시. birthdt 에서 3분기 가산하여 출력(9개월)
SELECT birthdt, DATE_ADD(birthdt, INTERVAL 3 QUARTER) FROM people;
예시. birthdt 에서 5개월 감산하여 출력
SELECT birthdt, DATE_SUB(birthdt, INTERVAL 5 MONTH) FROM people;
SELECT birthdt, birthdt - INTERVAL 5 MONTH FROM people;
DATETIME과 TIMESTAMP의 차이
DATETIME : 8bytes / 1000-01-01 ~ 9999-12-31
TIMESTAMP : 4bytes / 1970-01-01 ~ 2038-01-19
TIMESTAMP의 경우, 데이터가 생성된 순간의 날짜.시간을 손쉽게 출력하며
가장 대표적인 예로 social media의 코멘트마다 찍히는 시간
CREATE TABLE comments (
content VARCHAR(100),
created_at TIMESTAMP DEFAULT NOW()
);
++ 데이터가 수정되었을 경우, 수정날짜를 현재로 업데이트하기
CREATE TABLE comments (
content VARCHAR(100),
created_at TIMESTAMP DEFAULT NOW()
ON UPDATE CURRENT_TIMESTAMP
);
CREATE TABLE comments (
content VARCHAR(100),
created_at TIMESTAMP DEFAULT NOW()
ON UPDATE NOW()
);