mysql date-format()

http://blog.naver.com/k19111/40185299671

 

오늘의이야기 ( mysql date_format )

 

예전에 한번 포스팅 한거 같은데 먼가 좀 부실한거 같아서 좀 더 내실있게 써본다.. 리뉴얼 버젼이라고 할까나..

실제로 참 많은 곳이 mysql 데이터베이스를 쓴다.. 머 가볍고 쉽고 이런 것을 떠나서 공짜라서 많은 회사들이 많이 쓴다..

 

히스토리를 보면 Mysql 을 자바만든 썬 마이크로시스템에서 인수하고..

썬 마이크로 시스템을 오라클에서 인수하면서 같이 넘어갔다고 한다.. 자세한 히스토리가

알고 싶으시면 위키백과사전을 보면 될듯하다..

 

http://ko.wikipedia.org/wiki/MySQL   <- 요기링크

http://www.mysql.com/    <- 요기는 mysql 홈페이지

 

잡소리는 여기까지 하고 오늘의 주제는 mysql date_format 함수의 사용이다... 

mysql 을 사용하다 보면 꼭 사용할 일이 생긴다.. 나도 생겼으니 이를 보고 있는 다른 이들도 생길 것이다...

실제로 Document 를 살펴보자..

 

 

(이게 무슨 소린가 싶으다 ㅋㅋ) PHP.net 처럼 먼가 사용방법을 써놓든가 ;

실제로 날짜 검색만을 하고 싶은데 데이터타입이 datetime 으로 되어 있을때 심히 짜증난다...

혹시 모르는 사람을 위해서 데이터타입이 date 로 되어 있지 않고 datetime 으로 되어 있을때는

 

2013-03-26 13:00:00    <- 요런식으로 들어가게 된다...

이때는 걱정하지 말고 아래와 같이 쓰면 되겠다.. 두개중에 아무거나 하나 쓰면 되고

두개다 인덱스 처리되니 걱정하지 말자.. (단, 인덱스추가가 되어 있어야겠지 ;; )

 

1. where 검색컬럼 > '2000-01-01 00:00:00'  and 검색컬럼 < '2000-01-01 23:59:59'

2. where 검색컬럼 between '2000-01-01 00:00:00' and '2000-01-01 23:59:59'

 

자, 드디어 date_format 의 사용이다...

date_format 컬럼은 이상하게(?) 저장되어 있는 컬럼을 내가 원하는 타입으로 날짜와 시간을 보여주거나,

검색등을 원활하게 하고자 할때 사용된다..

 

이거 인덱스 안 걸리니 참고할 것!!  왼쪽컬럼에 함수가 걸려있으면 인덱스 안 걸리니까 쓰지 말고 위에껄로 쓰자.

이건 단지 예제이다. 예제 ..

 

select * from 테이블 where date_format(검색컬럼, '%Y-%m-%d') = '2011-04-26' limit 1;

(저것이 검색컬럼이 2011-04-26 날짜인것만 가져오는 쿼리. )
 
데이터 포맷 함수는 이렇게 조건절로 검색할때 말고, select 문에서 데이터를 원하는 형식으로

뽑아 올 때 유용할 꺼 같으다... 이왕이면 아래와 같은 상황에서 말이다.

 

SELECT DATE_FORMAT(regdate,'%M') FROM test_20110427 WHERE

regdate BETWEEN '2011-04-27' AND '2011-04-28'

 

 

 

또 다른 케이스를 한번 알아보자. 실제로 업무 하다 보면 데이터베이스에다가 별의별 방식으로 다 넣어 두는데

이런 케이스도 있다. varchar 으로 설정해놓고 구분없이 길~~게 적어놓는 방식이다.

 

 

 

value 에 들어간 데이터가 보이는 가... 처음에 긴가민가 했다.. 무슨 암호화 데이터도 아니고 데이트타입을 이따위로 --;

이미 몇백만건씩 데이터가 들어가고 있고 alter 처리도 불가능하고 데이터 뽑아 줄때마다 고생이다..

그래서 최초설계가 중요한 것이다. 최초설계는 디비 좀 잘 아는 시니어에게 맡기자....

(어떤 분이 이글 보고 댓글을 달았는데, 아래참조; 일부러 그렇게 했을수도 있다고 하니 참조하자..

하지만, 내 개인적인 입장에서는 이렇게 달아놓으면 알아보기도 힘들고, 데이터뽑아주기도 힘들다.

잘 몰라서 그렇게 햇다고 한적은 없으니, 먼가 이상한 오해를 안 했으면 좋겠다...)

 

아무튼 저렇게 암호화(?) 처럼 보이는 데이터를 날짜타입에 맞추어 출력하거나 그룹핑 시킬때 date_format 이 유용하다.

 

 

##복사해서 사용하라고^^ : select idx, date_format(value,'%Y-%m-%d %H:%i:%s') as value from test where idx = 3;

 

자.. 이쁘게 구분되어서 날짜타입과 시간타입이 딱딱 구분되어 표기되어지는 것이 보이는가?

실제로 함수를 사용하게 될때에  

 

date_format(검색컬럼, '표현식')

 

이렇게 사용하게 되고 표현식은 저 위에 상단에 있는 메뉴얼 보이는 가 거기서 하나씩 가져다가 쓰면 된다..

실제로 %Y %m %d  이렇게 저 메뉴얼 이외의 부분은 모두 검색식에 노출이 되게 된다..

바로 위에 것을 예제로 들면 - (하이픈) , : (콜론) 의 값은 검색식에 그대로 노출이 되어 있는 것이 보이지 않는가...

 

---------------------------------------------------------------------------------------------------------

 

자.. 마지막으로 업무중에 일어난 일을 예로 한번 설명해 보겠다...

보통 통계 데이터를 출력해 달라는 요청을 많이 받게 되는데 .. 이번에 있는 요청은 특이해서 기억에 좀 많이 남는다..

 

특이한 고객이었던 듯하다.. 보통 요청이 오게 되면 특정날짜로 구분을 지어서

'1달 데이터 출력해 주세요', '1주일 데이터 뽑아 주세요' 이러는데 이번꺼는 특이하게도..

1달 동안의 데이터중에서 요일별로 시간별로 구분을 지어서 데이터 통계를 보고 싶다고 한다...

 

그러니까 요점은 요일별로 어떤 시간때에 데이터가 가장 많이 남게 되는 것을 보고 싶은 것이었다..

갑갑했지만, 고민끝에 이때도 mysql_format 함수를 사용하여 데이터를 출력하였다..

 

select date_format(검색컬럼,'%W %H'),count(*) from 테이블명

where 검색컬럼 > '2013-01-01 00:00:00' and 검색컬럼 < '2013-01-31 23:59:59'

group by date_format(검색컬럼,'%W %H') order by date_format(검색컬럼,'%W %H') asc;

자. . 이 쿼리를 한번보자.. 실제로 되는 것을 확인했으니 의심하지 않아도 된다 ^^

%W 의 항목은 요일을 나타내는 것이고, %H 는 시간을 0~23 으로 표기하는 것을 뜻한다..

이를 대상으로 group by 처리하여 같은 요일의 같은 시간때의 데이터를 count(*) 처리하여 출력한다는 얘기가 되겠다!

 

먼가 두서없이 이리저리 써봤는데 잘 모르시는 사람들에게 도움이 됐으면 하는 바램이다..

 

마지막으로 이리저리 예제 만들어서 올려보겠다.. now() 는 기준일이 현재.. (각각 컬럼에 맞춰서 사용하길 바람)

 

select date_format(now(), '%Y-%m-%d'); 

select date_format(now(), '%Y/%m/%d');

select date_format(now(), '%Y-%m-%d %h:%i:%s'); 

select date_format(now(), '%W'); -- 오늘무슨요일

select date_format(now(), '%d');  -- 오늘몇일

select date_format(now(), '%D');  -- 영어표기 28th like that

select date_format(now(), '%M'); -- 무슨달 / March