DATE 데이터 타입 관련 함수

1. 연도, 월, 일 추출하기

DATE 타입의 값에서 연도(year)만, 혹은 월(month)만, 혹은 일(day)만 추출할 수 있습니다. 어떻게 할 수 있는지 하나씩 살펴볼게요.

(1) 1992년에 태어난 회원들만 조회하기

위 그림에서처럼 YEAR 함수를 사용하면 날짜 값에서 연도만 뽑아낼 수 있습니다. 

(2) 여름(6, 7, 8월)에 가입한 회원들만 조회하기 

위 그림처럼 MONTH 함수를 사용하면 날짜값에서 월만 뽑아낼 수 있습니다. 지금 그 뒤에는 이전 노트에서 배운 IN 조건 표현식을 사용해봤습니다. 위 그림을 보니 여름에 가입한 회원들만 잘 조회되네요. 

(3) 각 달의 후반부(15일~31일)에 가입했던 회원들만 조회하기

DAYOFMONTH 함수는 날짜값에서 일만 뽑아낼 수 있습니다. 그 뒤에는 이전에 배운 BETWEEN a AND b 구문을 사용해봤습니다. 각 달의 후반부(15일~31일) 사이에 가입한 회원들만 잘 조회되네요. 

2. 날짜 간의 차이 구하기

날짜 간의 차이를 구하는 함수도 있습니다. DATEDIFF라는 함수인데요. 여기서 DATE는 날짜, DIFF는 Difference의 줄임말로 '차이'라는 뜻입니다.

DATEDIFF(날짜 a, 날짜 b)를 사용하면 '날짜 a - 날짜 b'를 해서 그 차이 일수를 알려줍니다. 예를 들어,

DATEDIFF(’2018-01-05’, ’2018-01-03’)의 값은 2입니다.

member 테이블에서 각 회원이 가입한 일자가 2019년 1월 1일을 기준으로 몇 일 이후인지를 알아보겠습니다. 아래 SQL 문을 자세히 살펴보세요.

실행결과에는 총 3개의 컬럼이 보입니다. email, sign_up_day, 그리고 sign_up_day에서 2019년 1월 1일을 뺀 값이 담긴 컬럼, 이렇게 3개인데요. 꼭 테이블에 원래 있던 컬럼이 아니더라도 조회할 때는 이런 식으로 새로운 컬럼을 붙여서 볼 수도 있습니다. 

지금 보니까 가장 최근에는 cowboy라는 회원이 가입했고, 가입한지 가장 오래된 회원은 2011년에 가입한 xMan_series라는 회원이라는 것을 알 수 있습니다. 

그리고 이것 말고도 오늘 날짜를 기준으로 살펴보는 것도 가능한데요. 오늘 날짜를 구하는 함수는 CURDATE()입니다. 

이 노트가 작성된 날짜는 2020년 6월 2일입니다. 위 그림을 보니 오늘 날짜와의 차이도 잘 출력되었죠? 

이번엔 회원들이 몇 살이었을 때 코팡에 가입했는지를 알아볼까요? 가입일(sign_up_day)-생일(birthday) 값을, 1년이 365일이니까 365로 나눠주면 각 회원이 몇 살일 때, 코팡에 가입했는지 알 수 있습니다. 

결과를 보니까 많은 회원들이 20대, 30대일 때 많이 가입을 했네요. 

3. 날짜 더하기 빼기 

날짜에서 며칠을 더하고 빼는 것도 가능한데요. 더하는 함수는 DATE_ADD(), 빼는 함수는 DATE_SUB()입니다. 

예를 들어, 가입일(sign_up_day) 기준으로 300일 이후의 날짜를 구하려면 이렇게 쓰면 됩니다. 

지금 DATE_ADD(sign_up_day, INTERVAL 300 DAY)라고 적힌 부분은 sign_up_day 컬럼의 값에 300일을 더한 날짜를 나타냅니다. 

만약 가입일(sign_up_day) 기준 250일 이전의 날짜를 구하고 싶으면 이렇게 쓰면 됩니다.

특정 날짜에서 몇 일 후, 몇 일 전 날짜를 구해야할 때 이 함수들을 쓰면 유용하겠죠? 

4. UNIX Timestamp 값 

지금 member 테이블에서 날짜를 나타내는 컬럼(birthday, sign_up_day)은 있지만, 시간을 별도로 나타내는 컬럼은 없습니다. 날짜뿐만 아니라 시간까지 포함하는 컬럼이라면 DATETIME이라는 데이터 타입을 사용해야하는데요. DATETIME 타입의 컬럼에는 보통 '2018-12-31 23:54:59’ 이런 식으로 값들이 저장되어 있습니다.

그런데 문제는 어떤 테이블에는 날짜와 시간이 이렇게 예쁜 형식으로 적혀있는게 아니라, 1553526000 이런 식으로 상당히 큰 숫자값이 적혀있는 경우들이 꽤 많다는 겁니다. 이것 또한 날짜와 시간을 나타내는 값인데요. 이런 형식의 날짜시간 값을 UNIX Timestamp라고 합니다. UNIX Timestamp는 특정 날짜의 특정 시간을, 1970년 1월 1일을 기준으로, 총 몇 초가 지났는지로 나타낸 값입니다. 

정확히 어떤 건지 보여드리겠습니다. DATE 타입의 값을 Unix Timestamp로 바꿔주는 함수가 있는데요. sign_up_day 컬럼의 날짜값을 한번 Unix Timestamp로 변환해보겠습니다.

UNIX_TIMESTAMP라는 함수를 쓰면 되는데요. 지금 빨간 박스 안에 상당히 큰 숫자값들이 보입니다. 이 값들은 모두 각 날짜가 1970년 1월 1일을 기준으로 몇 초가 지난 것인지를 나타냅니다. 실무에서 여러분은 깔끔한 형식의 날짜 또는 시간뿐만 아니라 이런 Unix Timestamp를 보게될 수도 있습니다.

만약 여러분이 테이블에서 이런 Unix timestamp를 보게 된다면 이걸 사람이 읽을 수 있는 날짜 형태로 바꿔서 보면 되는데요.

그럴 때는 FROM_UNIXTIME 함수를 사용하면 됩니다. 

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의 기초 개념과 기본 함수들을 배우고, 여러분이 매 상황마다 필요한 함수들을 잘 검색하는 능력을 기른다면 여러분의 데이터 분석 능력은 급속도로 좋아질 겁니다.