📌 학습주제
1. 날짜/시간 데이터 타입
2. TIMESTAMP 함수 - 현재시간 표현
3. TIMESTAMP 함수 - 날짜 형식화
4. TIMESTAMP 함수 - 날짜 연산
SQL에서 날짜/시간을 나타내는 데이터 타입
1. STRING : 'yyyy-mm-dd', 'yyyy-mm-dd HH:MM:SS'
- 가장 단순하게 날짜/시간 데이터를 저장할 수 있는 방식으로, 형식에 제약이 없다.
- 시간 관련 함수들을 사용할 수 없다. (시간 관련 함수를 사용하기 위해서는 다른 데이터 타입으로 변경해야 함)
2. DATE : yyyy-mm-dd
- 연/월/일 정보만을 저장하는 방식 (시간 단위는 저장하지 않는다.)
- DATETIME, TIMESTAMP 타입과 비교했을 때 저장공간을 덜 차지한다는 장점이 있다.
- 따라서 정확한 시간까지는 필요 없거나, 대규모 데이터를 저장해야 할 때 저장공간을 절약시킬 수 있다.
3. DATETIME : YYYY-MM-DD HH:MM:SS
- 날짜/시간 정보를 모두 저장하며 정밀한 시간 정보를 제공한다.
- 시간 연산이 가능하다 -> 시간 간격을 고려한 데이터 분석이 가능하다.
- 더 많은 정보를 저장하므로 더 많은 저장공간을 차지한다. (따라서 대규모 데이터를 저장해야할 때 저장할 시간정보의 범위를 제약해줘야 한다.)
- 클릭 로그, 구매 로그, 서비스에서 발생하는 고객 행동 로그, 예약 시스템, 서버에서 발생하는 이벤트 로그에 사용된다.
- 저장된 시간에 대한 연산이 필요할 경우 적잘한 데이터 타입
4. TIMESTAMP : YYYY-MM-DD HH:MM:SS UTC
- DB종류에 따라 표기 방식이 다르다. MySQL에서는 DATETIME과 유사하지만 마지막에 타임존이 붙을 수 있다.
- 저장할 수 있는 시간 범위가 DATETIME보다 더 제한적이다. 따라서 TIMESTAMP가 차지하는 저장소 용량이 DATETIME보다 작다.
- 1970-01-01 부터 2038-01-19까지 표현 가능하다.
- 활용 케이스는 DATETIME과 유사하다.
TIMESTAMP 함수 - 현재 시간
1-1. NOW()
: UTC 기준으로 SQL 쿼리문이 실행되는 시점의 시간(=현재 시간)을 가져온다.
SELECT NOW();
UTC 기준이기 때문에 KST(한국 표준 시간) 보다 9시간 정도 느린 것을 알 수 있다.
1-2. CURRENT_TIMESTAMP()
: NOW() 함수와 동일하다.
1-3. CURTIME()
: DB 종류에 다라 서버의 시간을 반환하거나 날짜까지 포함해서 반환한다.
1-4. CURRENT_DATE() = CURDATE()
: yyyy-mm-dd 형식으로 반환하며 시간은 포함하지 않는다.
2. SYSDATE()
: 함수가 호출된 시간을 가져온다.
-- 처음 두 함수를 실행하고 2초 이후 다시 두 함수를 실행한다.
SELECT NOW(), SYSDATE(), SLEEP(2), NOW(), SYSDATE();
NOW()는 2초 앞뒤로 시간 차이가 나지 않지만,
SYSDATE()는 함수가 호출된 시간을 반환하므로 먼저 실행된 SYSDATE()와 2초의 시간 차이가 난다.
3-1. YEAR()
: 날짜에서 연도 추출
SELECT YEAR(NOW());
3-2. MONTH()
: 날짜에서 월 추출
3-3. DAY()
: 날짜에서 일 추출
4-1. HOUR()
: 시간에서 시 추출
4-2. MINUTE()
: 시간에서 분 추출
4-3. SECOND()
: 시간에서 초 추출
5. WEEKDAY()
: 오늘이 일주일 중 무슨 요일인지를 0부터 6사이 숫자로 반환한다. (0 = 월요일, 6 = 일요일)
SELECT WEEKDAY(NOW());
1을 반환했으므로 오늘은 화요일임을 알 수 있다.
6. MONTHNAME()
: 지금이 몇 월인지를 영문으로 반환한다.
7. DAYNAME()
: 지금이 무슨 요일인지를 영문으로 반환한다.
TIMESTAMP 함수 - 날짜 형식화
1. STR_TO_DATE(컬럼명, 데이트 포맷)
: 문자열 타입을 날짜 타입으로 변환시키는 함수
주의해야 할 점은, 실제 데이터의 형태에 따라 맞춰서 작성해줘야 한다는 것이다.
예제에서 실제 데이터가 '20231014' 형식이기 때문에 이에 맞게 '%Y%m%d'로 맞춰서 작성해주었다.
SELECT STR_TO_DATE(date, '%Y%m%d') as formatted
FROM orders
만약 실제 데이터의 형식과 다르게 '%Y-%m-%d' 와 같은 형식으로 작성한다면 결과는 어떨까?
SELECT STR_TO_DATE(date, '%Y-%m-%d') as formatted
FROM orders
2. DATE_FORMAT
: 지정된 형식으로 날짜를 출력한다.
| DATE_FORMAT | 의미 | 예시 |
| %Y | 연도(4자리) | 2024 |
| %y | 연도(끝 2자리) | 24 |
| %M | (MySQL 기준) 월(영문) | March |
| %m | 월(2자릿수) | 03 |
| %d | 일 (2자릿수) | 06 |
| %H | 시(24시간) | 05 |
| %T | hh:mm:ss | 13:20:33 |
| %s | 초 | 3 |
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %T') as d
TIMESTAMP 함수 - 날짜 연산
1. ADDDATE() = DATE_ADD()
: 특정 interval(시간 간격) 만큼 시간을 더한다.
SELECT ADDDATE('2024-03-06', 3) as added
** INTERVAL : 단위를 지정해서 연산할 수 있다.
SELECT DATE_ADD('2024-03-06', INTERVAL 3 SECOND) as added
2. SUBDATE() = DATE_SUB()
: 특정 interval 만큼 시간을 뺀다.
혹은 DATE_ADD()를 사용하되 음수 만큼 더해서 DATE_SUB()와 같은 연산을 수행하게 만들 수 있다.
먼저 DATE_SUB()의 예시부터 살펴보자.
SELECT DATE_SUB('2024-03-06', INTERVAL 1 HOUR) as subbed
이번에는 DATE_ADD()를 사용해서 시간 뺄셈 연산을 수행해보자.
SELECT DATE_ADD('2024-03-06', INTERVAL -1 HOUR) as subbed
동일한 결과가 나오는 것을 알 수 있다.
3. CONVERT_TZ(시간, from 타임존, to 타임존)
: 타임존을 변경해서 출력한다.
-- '+00:00' : 현재의 타임존
-- '+09:00' : 원하는 타임존
SELECT CONVERT_TZ(NOW(), '+00:00', '+09:00')
4. DATEDIFF(날짜1, 날짜2)
: 두 날짜 간의 차이를 날짜 단위로 반환한다. ( = 날짜1 - 날짜2)
SELECT DATEDIFF('2024-03-06', '2024-07-04') as diff
5. TIMEDIFF(시간1, 시간2)
: 두 시간 간의 차이를 시간 단위로 반환한다. (= 시간1 - 시간2)
SELECT TIMEDIFF('2024-03-06 11:05:05', '2024-03-06 19:00:07') as diff
6. TIME_TO_SEC()
: 시간을 초 단위로 반환한다.
SELECT TIME_TO_SEC(TIMEDIFF('2024-03-06 11:05:05', '2024-03-06 19:00:07')) as diff
Q. 시간 연산 혹은 시간 단위 변환 함수들은 어디에 사용될 수 있을까?
A. 사용자 행동 로그는 TIMESTAMP 혹은 DATETIME 형태로 저장되는 경우가 많다. 초 단위의 작은 시간 안에서도 사용자의 행동이 달라지기 때문이다.
따라서 이를 분석할 때 저장된 시간 간의 차이를 구한 후 초 단위로 변경해준다. 클릭 후 다음 클릭까지 몇 초가 걸리는지, 클릭 후 구매까지 몇 초가 걸리는지 등의 정보를 분석할 수 있다.
만약, 클릭 후 다음 클릭까지 시간이 오래 걸리거나, 고객들이 많이 이탈하는 구간에서는 문제를 찾아내기 위해 추가적인 분석으로 이어지는 경우가 많다.
