[MySQL] 날짜함수

날짜 더하기 / 빼기

ADDDATE / SUBDATE

  • 날짜 더하기 / 빼기
SELECT ADDDATE('2022-01-02', INTERVAL 31 DAY) 
=> 2022-02-02

SELECT ADDDATE('2022-01-02', 31)
=> 2022-02-02

ADDTIME / SUBTIME

  • 시간 더하기 / 빼기
SELECT ADDTIME('2021-01-01 00:00:00', '100')
=> 2021-01-01 00:01:00

SELECT ADDTIME('2021-01-01 00:00:00', '1000')
=> 2021-01-01 00:10:00

SELECT ADDTIME('2021-01-01 00:00:00', '10000')
=> 2021-01-01 01:00:00

SELECT ADDTIME('2021-01-01 00:00:00', '100000')
=> 2021-01-01 10:00:00

SELECT ADDTIME('2021-01-01 00:00:00', '1000000')
=> 2021-01-05 04:00:00

DATEDIFF

  • 날짜 - 날짜
  • 날짜 값만 고려(시간은 고려 안함)
SELECT DATEDIFF('2022-01-01', '2021-12-01') => 31
SELECT DATEDIFF('2022-01-01', '2020-05-15') => 596

TIMEDIFF

  • 시간 - 시간
SELECT TIMEDIFF('2008-12-31 23:59:59', '2008-12-30 01:01:01')
=> '46:58:58'

DATE_ADD / DATE_SUB

SELECT DATE_ADD(NOW(), INTERVAL 10 SECOND)
SELECT DATE_ADD(NOW(), INTERVAL 10 MINUTE)
SELECT DATE_ADD(NOW(), INTERVAL 10 HOUR)
SELECT DATE_ADD(NOW(), INTERVAL 10 DAY)
SELECT DATE_ADD(NOW(), INTERVAL 10 MONTH)
SELECT DATE_ADD(NOW(), INTERVAL 10 YEAR)

SELECT DATE_ADD(NOW(), INTERVAL '1 10' HOUR_MINUTE);
SELECT DATE_ADD(NOW(), INTERVAL '1 10' DAY_HOUR);
SELECT DATE_ADD(NOW(), INTERVAL '1 10' YEAR_MONTH);

날짜 / 시간 반환

CURDATE / CURRENT_DATE

  • 현재 날짜 반환
  • 기본형식은 'YYYY-MM-DD'
SELECT CURDATE() => 2022-01-01
SELECT CURRENT_DATE() => 2022-01-01
SELECT CURRENT_DATE => 2022-01-01

SELECT ADDDATE(CURDATE(), 15) => 2022-01-16
SELECT DATE_FORMAT(CURDATE(), '%Y%m%d') => 20220101

CURTIME / CURRENT_TIME

  • 현재 시간 반환
  • 기본형식은 'hh:mm:ss'
SELECT CURTIME() => 21:09:28
SELECT CURRENT_TIME() => 21:09:28
SELECT CURRENT_TIME => 21:09:28

CURRENT_TIMESTAMP / NOW

  • 현재 날짜와 시간
  • CURRENT_TIMESTAMP()는 NOW와 동일
SELECT CURRENT_TIMESTAMP() => 2022-01-01 21:09:28
SELECT NOW() => 2022-01-01 21:09:28

날짜 / 시간 뽑아내기

DATE

  • datetime에서 date를 뽑아냄
SELECT DATE('2022-01-01 21:09:28') => 2022-01-01

TIME

  • datetime에서 time을 뽑아냄
SELECT TIME('2003-12-31 01:02:03') => '01:02:03'

YEAR / MONTH / DAY

  • 연 / 월 / 일 을 뽑아냄
SELECT YEAR('2021-12-31') => 2021
SELECT MONTH('2021-12-31') => 12
SELECT DAY('2021-12-31') => 31

HOUR / MINUTE / SECOND

  • 시 / 분 / 초 를 뽑아냄
SELECT HOUR('12:30:50') => 12
SELECT MINUTE('12:30:50') => 30
SELECT SECOND('12:30:50') => 50

DAYOFWEEK

  • 요일을 반환
  • 1 = Sunday, 2 = Monday, …, 7 = Saturday
SELECT DAYOFWEEK('2021-12-31') => 6

DAYOFYEAR

  • 해당 일이 일년의 몇 번째 날인지 반환
SELECT DAYOFYEAR('2021-12-25') => 359

LAST_DAY

  • 해당 달의 마지막 날짜를 반환
  • 기본형식은 'YYYY-MM-DD'
SELECT LAST_DAY('2003-02-05') => 2003-02-28
SELECT LAST_DAY('2004-02-05') => 2004-02-29

MONTHNAME

  • 달의 이름을 반환 (영어)
SELECT MONTHNAME('2021-12-31') => December

QUARTER

  • 해당 날짜의 분기를 반환
SELECT QUARTER('2008-04-01') => 2

포맷

DATE_FORMAT

CONVERT_TZ

  • timezone 변경
  • CONVERT_TZ(dt, from_tz, to_tz)
SELECT CONVERT_TZ('2022-01-01 12:00:00','GMT','MET')
=> 2022-01-01 13:00:00

GET_FORMAT

  • format string을 리턴
  • USA, JIS, ISO, EUR, INTERNAL
SELECT GET_FORMAT(DATE, 'USA') => %m.%d.%Y
SELECT GET_FORMAT(DATETIME, 'USA') => %Y-%m-%d %H.%i.%s
SELECT GET_FORMAT(TIME, 'USA') => %h:%i:%s %p

SELECT DATE_FORMAT(NOW(), GET_FORMAT(DATETIME, 'USA'))
=> 2022-01-01 21.59.12

STR_TO_DATE

SELECT STR_TO_DATE('01,5,2013','%d,%m,%Y')
=> '2013-05-01'

STR_TO_DATE('May 1, 2013','%M %d,%Y')
=> '2013-05-01'

SELECT STR_TO_DATE('abc','abc')
=> '0000-00-00'

links

social