SQL Working With Dates

날짜 데이터

• 기존 데이터베이스의 날짜 컬럼의 포멧과 일치시키는 것이 중요

MySQL 날짜 데이터 형식

• DATE: YYYY-MM-DD
• DATETIME: YYYY-MM-DD HH:MI:SS
• TIMESTAMP: YYYY-MM-DD HH:MI:SS
• YEAR: YYYY or YY

SQL Server 날짜 데이터 형식

• DATE: YYYY-MM-DD
• DATETIME: YYYY-MM-DD HH:MI:SS
• SMALLDATETIME: YYYY-MM-DD HH:MI:SS
• TIMESTAMP: a unique number

MySQL 날짜 관련 함수

• DATE 값을 기대하는 함수들은 일반적으로 DATETIME 값을 수용하고, TIME 부분은 무시한다.
• TIME 값을 기대하는 함수들은 일반적으로 DATETIME 값을 수용하고, DATE 부분은 무시한다.
• 현재 날짜나 시간을 반환(return)하는 함수들은 쿼리가 실행될 때 단 한번만 그 값을 구한다.
  • 한 쿼리 안에 NOW()와 같은 함수가 여러번 사용되었을 경우에도 모두 같은 결과값을 참조한다는 것을 의미한다.
  • 이 원칙은 CURDATE(), CURTIME(), UTC_DATE(), UTC_TIME(), UTC_TIMESTAMP() 등의 함수에도 적용된다.

날짜와 시간의 형식화

• DATE_FORMAT(date, format): 날짜를 포맷에 맞춰 문자열로 전환

SELECT DATE_FORMAT(date, '%Y-%m-%d') FROM table;
-- YYYY-mm-dd

SELECT DATE_FORMAT(date, '%Y-%m-%d %T') FROM table;
SELECT DATE_FORMAT(date, '%Y-%m-%d %H:%i:%s') FROM table;
-- YYYY-mm-dd 00:00:00

연도별, 월별, 주별 일수

• DAYOFYEAR(date): 주어진 date의 일자가 해당 연도에서 몇 번째 날인지 반환
  • 1~366
• DAYOFMONTH(date): 주어진 date의 일자가 해당 월에서 몇 번째 날인지 반환
  • 0~31
• DAYOFWEEK(date): 주어진 date의 일자가 해당 주에서 몇 번째 날인지 반환
  • 일요일 = 1, 토요일 = 7

특정 날짜와 시간 정보

• DATE(expression): 주어진 expression에 해당하는 날짜 정보 반환
  • expresion: a valid date/datetime value. Returns NULL if expression is not a date or a datetime
    (예) SELECT DATE("2017-06-15 09:34:21"); → 2017-06-15
      SELECT DATE("The date is 2017-06-15"); → NULL
• MONTH(date): 주어진 date에서 월에 해당하는 숫자를 반환
  • 0~12
• WEEKDAY(date): 주어진 date에서 요일에 해당하는 숫자를 반환
  • 월요일 = 0, 일요일 = 6
• LAST_DAY(date): 주어진 date에서 해당 월의 마지막 날짜 정보 반환
• SEC_TO_TIME(seconds): 주어진 seconds를 기준으로 시간 정보 반환
  • HH:MM:SS 형식
• EXTRACT(part FROM date): 주어진 date에서 원하는 part에 해당하는 숫자 반환
  • part에 들어갈 수 있는 표현: microsecond, second, minute, hour, day, week, month, quarter, year, second_microsecond 등

현재 날짜와 시간 정보

• NOW(), CURRENT_TIMESTAMP(): 현재 날짜와 시간 반환
• CURDATE(), CURRENT_DATE(): 현재 날짜 반환
• CURTIME(), CURRENT_TIME(): 현재 시각 반환

특정 날짜와 시간 연산 ★

• ADDDATE(date, INTERVAL value addunit): date에 value addunit만큼 시간/날짜를 추가한 date를 반환
  • INTERVAL value addunit 대신 days도 가능
• ADDTIME(datetime, addtime)
• SUBDATE(date, INTERVAL value unit)
• SUBTIME(datetime, addtime)
• PERIOD_ADD(period, number): 주어진 period에 number만큼 월을 추가한 숫자를 반환
  • period의 형식은 YYMM 또는 YYYYMM만 가능

SELECT ADDDATE("2022-06-11", INTERVAL 10 DAY),
	   ADDTIME("2022-06-11 12:34:56", "10 05:05");

-- 2022-06-21
-- 2022-06-21 17:39:56

ADDDATE() vs DATE_ADD() in MySQL
ADDDATE()는 두번째 인자에서 INTERVAL과 함께 사용되면 DATE_ADD()의 별칭이 된다. 마찬가지로 SUBDATE()는 DATE_SUB()의 별칭이다.
DATE_ADD() → DATE_ADD(date,INTERVAL expr unit)
ADDDATE() → ADDDATE(date,INTERVAL expr unit) or ADDDATE(expr,days)
This second form is only available when using the ADDDATE() function, and it allows you to use a shorthand way to specify the number of days to add to the date.

특정 날짜나 시간의 차이 연산 ★

• PERIOD_DIFF(period1, period2): 두 기간의 차이를 숫자로 반환
  • period의 형식은 YYMM 또는 YYYYMM만 가능
  • period1과 period2는 같은 형식이어야 함
• DATEDIFF(date1, date2): 두 날짜 사이의 일수를 숫자로 반환(date1 - date2)
• TIMEDIFF(time1, timd2)

출처 1
출처 2

연습

id	name	major	hire_date
1	르탄이	피부과	2018-05-10
2	배캠이	성형외과	2019-06-15
3	구구이	안과	2020-07-20

→ doctors 테이블

• doctors 테이블에서 현재 날짜 기준으로 5년 이상 근무(hire_date)한 의사 수를 계산하는 쿼리
  • 의사 수를 계산 → SELECT COUNT(*)
  • 현재 날짜 기준으로 → CURDATE()
  • 현재 날짜 기준으로 5년 이상 근무 → hire_date <= SUBDATE(CURDATE(), INTERVAL 5 YEAR);

SELECT COUNT(*) num_of_doctors
FROM doctors
WHERE hire_date <= SUBDATE(CURDATE(), INTERVAL 5 YEAR);

• doctors 테이블에서 각 의사의 근무 기간을 계산하는 쿼리

SELECT name, DATEDIFF(CURDATE(), hire_date) working_days
FROM doctors;