2023-03-02 목요일

💡 입양 시각 구하기(1)

---

1. ANIMAL_OUT 테이블 전체 출력하기

SELECT
*
  FROM ANIMAL_OUT

💻 출력

---

2. 09:00부터 19:59까지의 데이터만 출력하기

SELECT *
  FROM ANIMAL_OUTS
  WHERE datetime 
    BETWEEN DATE_FORMAT(datetime, '%Y-%m-%d 09:00:00') AND DATE_FORMAT(datetime, '%Y-%m-%d 19:59:59'

💻 출력

---

3. 시간대별로 그룹화하기

1. DATE_FORMAT 사용

SELECT HOUR(datetime) AS hour
  FROM ANIMAL_OUTS
  WHERE datetime 
    BETWEEN DATE_FORMAT(datetime, '%Y-%m-%d 09:00:00') AND DATE_FORMAT(datetime, '%Y-%m-%d 19:59:59')
GROUP BY hour

2. HOUR(datetime) 사용

SELECT HOUR(datetime) AS hour
  FROM ANIMAL_OUTS
  WHERE HOUR(datetime) BETWEEN 09 AND 20
GROUP BY hour

💻 출력

---

4. 시간대 별로 입양 건수(count) 출력하기

SELECT HOUR(datetime) AS hour
    ,COUNT(datetime) AS count
  FROM ANIMAL_OUTS
  WHERE datetime 
    BETWEEN DATE_FORMAT(datetime, '%Y-%m-%d 09:00:00') AND DATE_FORMAT(datetime, '%Y-%m-%d 19:59:59')
GROUP BY hour

💻 출력

---

5. 시간대 순으로 정렬하기

SELECT HOUR(datetime) AS hour
    ,COUNT(datetime) AS count
  FROM ANIMAL_OUTS
  WHERE datetime 
    BETWEEN DATE_FORMAT(datetime, '%Y-%m-%d 09:00:00') AND DATE_FORMAT(datetime, '%Y-%m-%d 19:59:59')
GROUP BY hour
ORDER BY hour

💻 출력

---

💡 문제점

🟦 09:00부터 19:59까지 출력하기

---

💡 해결

1. BETWEEN a AND b

BETWEEN '2013-12-22 11:30:00' AND '2017-09-27 19:09:00'

- BETWEEN a AND b : a부터 b까지

2. WHERE 조건절에 DATE_FORMAT 사용하기

WHERE datetime 
  BETWEEN DATE_FORMAT(datetime, '%Y-%m-%d 09:00:00') AND DATE_FORMAT(datetime, '%Y-%m-%d 19:59:59')

- DATE_FORMAT(컬럼명, 형식) : 날짜를 지정한 형식으로 출력

3. HOUR(컬럼명) 사용하기

WHERE HOUR(datetime) BETWEEN 09 AND 20

- datetime 컬럼의 HOUR만 출력한 후 09~20 사이의 컬럼들만 반환