[LEVEL 2][String, Date] 자동차 평균 대여 기간 구하기

https://school.programmers.co.kr/learn/courses/30/lessons/157342

프로그래머스

SELECT car_id
    , round(avg(DATEDIFF(end_date, start_date)+1), 1) AVERAGE_DURATION
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
group by car_id
having average_duration >= 7
order by 2 desc, 1 desc

- 가 아니라 date 함수 써야 됨!

 

날짜 관련 함수

1. 기준 날짜에서 더하기

DATE_ADD(date, INTERVAL value addunit)
DATE_SUB(date, INTERVAL value addunit)

• INTERVAL 
  
  • MICROSECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • MONTH
  • QUARTER
  • YEAR
  • SECOND_MICROSECOND
  • MINUTE_MICROSECOND
  • MINUTE_SECOND
  • HOUR_MICROSECOND
  • HOUR_SECOND
  • HOUR_MINUTE
  • DAY_MICROSECOND
  • DAY_SECOND
  • DAY_MINUTE
  • DAY_HOUR
  • YEAR_MONTH
• ADD INTERVAL - 
  = SUB

2. 날짜 연산

# date1 - date2
DATEDIFF(date1, date2)
TIMEDIFF(date1, date2)

• datediff : 날짜 계산
• timediff : 시간 계산

3. 날짜 출력

DATE_FORMAT(date, format)

%a	Abbreviated weekday name (Sun to Sat)
%b	Abbreviated month name (Jan to Dec)
%c	Numeric month name (0 to 12)
%D	Day of the month as a numeric value, followed by suffix (1st, 2nd, 3rd, ...)
%d	Day of the month as a numeric value (01 to 31)
%e	Day of the month as a numeric value (0 to 31)
%f	Microseconds (000000 to 999999)
%H	Hour (00 to 23)
%h	Hour (00 to 12)
%I	Hour (00 to 12)
%i	Minutes (00 to 59)
%j	Day of the year (001 to 366)
%k	Hour (0 to 23)
%l	Hour (1 to 12)
%M	Month name in full (January to December)
%m	Month name as a numeric value (00 to 12)
%p	AM or PM
%r	Time in 12 hour AM or PM format (hh:mm:ss AM/PM)
%S	Seconds (00 to 59)
%s	Seconds (00 to 59)
%T	Time in 24 hour format (hh:mm:ss)
%U	Week where Sunday is the first day of the week (00 to 53)
%u	Week where Monday is the first day of the week (00 to 53)
%V	Week where Sunday is the first day of the week (01 to 53). Used with %X
%v	Week where Monday is the first day of the week (01 to 53). Used with %x
%W	Weekday name in full (Sunday to Saturday)
%w	Day of the week where Sunday=0 and Saturday=6
%X	Year for the week where Sunday is the first day of the week. Used with %V
%x	Year for the week where Monday is the first day of the week. Used with %v
%Y	Year as a numeric, 4-digit value (2022)
%y	Year as a numeric, 2-digit value (22)

출처: https://www.w3schools.com/sql/func_mysql_date_format.asp