SQL을 어느 정도 배우고 나서도 많은 분들이 실무에서 SQL을 자유롭게 사용하지 못하는 경우가 많습니다.
그 주된 이유 중 하나는 바로 SQL에서 제공하는 함수를 자유자재로 사용하지 못하기 때문입니다.
사실 SQL의 핵심 기능들은, 다 배우고 나면 그렇게 어렵지는 않습니다. 하지만 SQL 문을 아무리 잘 쓰더라도 저장된 데이터를 내가 원하는 형태로 변환하기 위해 필요한 함수를 모르면 원하는 작업을 할 수가 없습니다. 그래서 SQL에서 쓸 수 있는 다양한 함수들을 익히는 것도 중요한데요.
이번 토픽에서는 SQL을 쓸 때, 여러분이 필수적으로 알아야할 함수들을 차근차근 노트로 정리해나갈 예정입니다.
일단 이번 노트에서는 DATE 타입의 값들을 다루는 대표적인 함수들을 다뤄보겠습니다.
우리가 테이블을 다루다 보면 날짜를 나타내는 값들, 정확히 말해 DATE 데이터 타입을 갖는 컬럼의 값들을 다뤄야할 일들이 많습니다.
이때 사용할 수 있는 함수들을 배워보겠습니다.
- 연도, 월, 일 추출하기 DATE 타입의 값에서 연도(year)만, 혹은 월(month)만, 혹은 일(day)만 추출할 수 있습니다. 어떻게 할 수 있는지 하나씩 살펴볼게요.
(1) 1992년에 태어난 회원들만 조회하기
SELECT * FROM copang_main.member WHERE YEAR(birthday) = "1992";
위 그림에서처럼 YEAR 함수를 사용하면 날짜 값에서 연도만 뽑아낼 수 있습니다.
(2) 여름(6, 7, 8월)에 가입한 회원들만 조회하기
SELECT * FROM copangmain.member WHERE MONTH(signup_day) IN (6, 7, 8);
위 그림처럼 MONTH 함수를 사용하면 날짜값에서 월만 뽑아낼 수 있습니다. 지금 그 뒤에는 이전 노트에서 배운 IN 조건 표현식을 사용해봤습니다. 위 그림을 보니 여름에 가입한 회원들만 잘 조회되네요.
(3) 각 달의 후반부(15일~31일)에 가입했던 회원들만 조회하기
SELECT * FROM copangmain.member WHERE DAYOFMONTH(signup_day) BETWEEN 15 AND 31;
DAYOFMONTH 함수는 날짜값에서 일만 뽑아낼 수 있습니다. 그 뒤에는 이전에 배운 BETWEEN a AND b 구문을 사용해봤습니다. 각 달의 후반부(15일~31일) 사이에 가입한 회원들만 잘 조회되네요.
- 날짜 간의 차이 구하기 날짜 간의 차이를 구하는 함수도 있습니다. DATEDIFF라는 함수인데요. 여기서 DATE는 날짜, DIFF는 Difference의 줄임말로 '차이'라는 뜻입니다.
DATEDIFF(날짜 a, 날짜 b)를 사용하면 '날짜 a - 날짜 b'를 해서 그 차이 일수를 알려줍니다. 예를 들어,
DATEDIFF(’2018-01-05’, ’2018-01-03’)의 값은 2입니다.
member 테이블에서 각 회원이 가입한 일자가 2019년 1월 1일을 기준으로 몇 일 이후인지를 알아보겠습니다. 아래 SQL 문을 자세히 살펴보세요.
SELECT email, signupday, DATEDIFF(signupday, "2019-01-01") FROM copang_main.member;
실행결과에는 총 3개의 컬럼이 보입니다. email, signupday, 그리고 signupday에서 2019년 1월 1일을 뺀 값이 담긴 컬럼, 이렇게 3개인데요. 꼭 테이블에 원래 있던 컬럼이 아니더라도 조회할 때는 이런 식으로 새로운 컬럼을 붙여서 볼 수도 있습니다.
지금 보니까 가장 최근에는 cowboy라는 회원이 가입했고, 가입한지 가장 오래된 회원은 2011년에 가입한 xMan_series라는 회원이라는 것을 알 수 있습니다.
SELECT eamil, signupday, CURDATE(), DATEDIFF(signupday, CURDATE()) FROM copang_main.member;
그리고 이것 말고도 오늘 날짜를 기준으로 살펴보는 것도 가능한데요. 오늘 날짜를 구하는 함수는 CURDATE()입니다.
이 노트가 작성된 날짜는 2020년 6월 2일입니다. 위 그림을 보니 오늘 날짜와의 차이도 잘 출력되었죠?
이번엔 회원들이 몇 살이었을 때 코팡에 가입했는지를 알아볼까요? 가입일(signupday)-생일(birthday) 값을, 1년이 365일이니까 365로 나눠주면 각 회원이 몇 살일 때, 코팡에 가입했는지 알 수 있습니다.
SELECT eamil, signupday, DATEDIFF(signupday, birthday) / 365 FROM copang_main.member;
결과를 보니까 많은 회원들이 20대, 30대일 때 많이 가입을 했네요.
- 날짜 더하기 빼기 날짜에서 며칠을 더하고 빼는 것도 가능한데요. 더하는 함수는 DATEADD(), 빼는 함수는 DATESUB()입니다.
예를 들어, 가입일(signupday) 기준으로 300일 이후의 날짜를 구하려면 이렇게 쓰면 됩니다.
SELECT email, signupday, DATEADD(signupday, INTERVAL 300 DAY) FROM copangmain.member;
지금 DATEADD(signupday, INTERVAL 300 DAY)라고 적힌 부분은 signup_day 컬럼의 값에 300일을 더한 날짜를 나타냅니다.
만약 가입일(signupday) 기준 250일 이전의 날짜를 구하고 싶으면 이렇게 쓰면 됩니다.
SELECT email, signupday, DATESUB(signupday, INTERVAL 250 DAY) FROM copangmain.member;
특정 날짜에서 몇 일 후, 몇 일 전 날짜를 구해야할 때 이 함수들을 쓰면 유용하겠죠?
- UNIX Timestamp 값 지금 member 테이블에서 날짜를 나타내는 컬럼(birthday, signupday)은 있지만, 시간을 별도로 나타내는 컬럼은 없습니다. 날짜뿐만 아니라 시간까지 포함하는 컬럼이라면 DATETIME이라는 데이터 타입을 사용해야하는데요. DATETIME 타입의 컬럼에는 보통 '2018-12-31 23:54:59’ 이런 식으로 값들이 저장되어 있습니다.
그런데 문제는 어떤 테이블에는 날짜와 시간이 이렇게 예쁜 형식으로 적혀있는게 아니라, 1553526000 이런 식으로 상당히 큰 숫자값이 적혀있는 경우들이 꽤 많다는 겁니다. 이것 또한 날짜와 시간을 나타내는 값인데요. 이런 형식의 날짜시간 값을 UNIX Timestamp라고 합니다. UNIX Timestamp는 특정 날짜의 특정 시간을, 1970년 1월 1일을 기준으로, 총 몇 초가 지났는지로 나타낸 값입니다.
정확히 어떤 건지 보여드리겠습니다. DATE 타입의 값을 Unix Timestamp로 바꿔주는 함수가 있는데요. signupday 컬럼의 날짜값을 한번 Unix Timestamp로 변환해보겠습니다.
SELECT email, signupday, UNIXTIMESTAMP(signupday FROM copangmain.member);
UNIX_TIMESTAMP라는 함수를 쓰면 되는데요. 지금 빨간 박스 안에 상당히 큰 숫자값들이 보입니다. 이 값들은 모두 각 날짜가 1970년 1월 1일을 기준으로 몇 초가 지난 것인지를 나타냅니다. 실무에서 여러분은 깔끔한 형식의 날짜 또는 시간뿐만 아니라 이런 Unix Timestamp를 보게될 수도 있습니다.
만약 여러분이 테이블에서 이런 Unix timestamp를 보게 된다면 이걸 사람이 읽을 수 있는 날짜 형태로 바꿔서 보면 되는데요.
그럴 때는 FROM_UNIXTIME 함수를 사용하면 됩니다.
SELECT email, signupday, FROMUNIXTIME(UNIXTIMESTAMP(signupday)) FROM copang_main.member;
Unix timestamp를 다시 집어넣었더니 원래 날짜가 그대로 잘 나왔습니다. 대신 이번엔 시간 정보도 포함해서(00:00:00) 출력되었네요.
혹시 날짜와 시간을 나타내는 컬럼이라고 하는데 뭔가 큰 숫자가 들어있다면, Unix timestamp가 아닌지 의심해보세요. 그리고 방금 배운 함수로 DATETIME 형식으로 변환해서 보시면 됩니다.
자, 이때까지 DATE 타입의 값들을 다룰 수 있는 유용한 함수들을 알아보았습니다. 이밖에도 '시간을 나타내는 TIME 타입', 방금 설명했던 '날짜, 시간을 함께 나타내는 DATETIME 타입', 그리고 이것들과 관련된 또다른 함수들도 많습니다. 혹시 이런 내용들에 관해 더 자세하게 알고 싶은 분들은 아래 MySQL 공식 매뉴얼을 참조하세요.
날짜, 시간 관련 데이터 타입 : https://dev.mysql.com/doc/refman/8.0/en/date-and-time-types.html
날짜, 시간 관련 함수 : https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html
그리고 MySQL이 아닌 다른 DBMS를 쓰는 분들은 이 페이지 내용을 보고 그대로 따라하시면 안 되고, 여러분이 쓰시는 DBMS의 매뉴얼 페이지를 참조해서 원하는 함수를 찾아야합니다. 이번 토픽에서 SQL의 기초 개념과 기본 함수들을 배우고, 여러분이 매 상황마다 필요한 함수들을 잘 검색하는 능력을 기른다면 데이터 분석 능력은 급속도로 좋아질 겁니다.