[ mysql ] 1년 사이에 등록된 데이터 리스트 뽑기

상황

- 2021년 1월 부터 2021년 12월 사이에 등록된 데이터 리스트를 뽑아야 한다.

- 저장된 데이터는 GMT 00:00 이고, 한국 시간으로 보여주어야 한다.

- 이벤트의 시작일과 종료일은 날짜만 있는 경우 날짜만 표시하고, 시간이 있는 데이터인 경우 시간까지 모두 표시해야한다.

 

 

찾아본 것 

🌈  조건 검색 ( case when then end )

체크해야할 조건이 많은 경우, 특정 조건에 부합할 때 특정한 값을 반환한다. 

- 양식

  case

     when 조건

     then 반환값

     when 조건

     then 반환값

  else  조건에 해당 안되는 경우 반환 값   end  

 

- when과 then은 무한대로 쓸수 있다.

- 프로그래밍 언어의 switch /case문과 비슷하지만 mysql에서는 하나의 조건에 부합하는 값만 반환한다.

- else가 있으면 모든 조건에 해당하지 않는 경우에 else가 반환된다. 

- else가 없고, case 조건에 부합하는 값이 없으면 null을 반환한다.

 

- 활용

case when column_name is null 
then event.start_datetime 
else DATE_FORMAT(event.start_date, '%Y-%m-%d') end 

 

 

🌈  null인 경우를 비교하기 ( is null )

null은 "="부호로 비교할 수 없다. 

is null을 사용하여 null인지 확인할 수 있다.

column_name is null  

 

만약 null이 아닌 경우를 설정한다면  is not 을 추가하면 된다, 

column_name is not null  

 

 

- 활용 

select * from tb_name

where

        column_name is null 

 

 

🌈  특정 기간으로 검색하기 ( between )

where column_name between  '2021-01-01' and '2021-12-31' 

 

 

🌈  날짜 바꾸기 : 타임존 적용 ( CONVERT_TZ ) 

세계 협정시는 그리치니 표준시(GMT) 와 협정세계시 (UTC)로 나뉜다. 하지만 둘다 동일한 시간이다.
시간대는 영국의 그리니치 천문대를 기준으로 시작하여 자오선에 따른 시간대를 선정한다.

- 영국시간 +00:00

- 한국시간 +09:00

 

CONVERT_TZ( 대상데이터, 현재시간대, 변경시간대)

 

CONVERT_TZ(NOW(),'+00:00','+09:00')

 

 

🌈  날짜를 특정 데이터 포맷으로 출력하기 ( DATE_FORMAT ) 

DATE_FORMAT(대상데이터, 포맷)

- 대상 데이터를 원하는 포맷으로 바꿀 수 있다.

- %Y 는 연 , %m 월, %d 일, %H.시 , %i 분, %s 초 ( https://lightblog.tistory.com/155 ) 

 

DATE_FORMAT(event.start_date, '%Y-%m-%d')

 

 

🌈  바꾼 날짜로 검색하기

where

DATE_FORMAT(scheduleList.endDate,'%Y') = '2021'

 

* 검색결과에서 검색하기

 

 

* 쿼리 플랜 보기 : EXPLAIN

 

 

 

결과 

select * 
from 
 ( select
	event.project_no 		as projectNo
	, (select p.project_name from tb_project p where p.project_no = event.project_no) as projectName		, event.event_no  			as scheduleNo
	, event.event_title 			as scheduleTitle
	, case when event.start_date is null 
		then convert_tz(event.start_datetime , '+00:00' , '+09:00')
		else DATE_FORMAT(event.start_date, '%Y-%m-%d') end as startDate
	, case when event.end_date is null 
		then convert_tz(event.end_datetime , '+00:00', '+09:00')
		else DATE_FORMAT(event.end_date, '%Y-%m-%d') end as endDate
	, event.reg_user_no				as regUserNo
	, (select userInfo.user_name from tb_user_info userInfo where userInfo.user_no = event.reg_user_no ) as regUserName
	, convert_tz(event.reg_date, '+00:00' , '+09:00') 	as regDate
  from tb_event event
  where 
	event.project_no = 30038399
  ) eventList
where
  DATE_FORMAT(eventList.startDate,'%Y') = '2021' 
  or DATE_FORMAT(eventList.endDate,'%Y') = '2021'	
order by startDate desc ;