[mysql] unixtime 사용법 정리 [펌]

[mysql] unixtime 사용법 정리 [펌]

 

// 테이블생성

CREATE TABLE `test` ( 
`regdate` INT NOT NULL
) TYPE = MYISAM ; // 데이타 삽입 
INSERT INTO `test` ( `regdate` ) VALUES ('1220799600 '); 
INSERT INTO `test` ( `regdate` ) VALUES (unix_timestamp()); 

 

// 년월일 시분초

select DATE_FORMAT(FROM_UNIXTIME(regdate), '%Y-%m-%d %H:%i:%s') regdate from

 

// 검색시

SELECT DATE_FORMAT( FROM_UNIXTIME( regdate ) , '%Y-%m-%d %H:%i:%s' ) regdate 
FROM test 
WHERE DATE_FORMAT( FROM_UNIXTIME( regdate ) , '%Y%m%d' ) = DATE_FORMAT( now( ) , '%Y%m%d' ) 

 

// BETWEEN 검색

SELECT DATE_FORMAT( FROM_UNIXTIME( regdate ) , '%Y-%m-%d %H:%i:%s' ) regdate 
FROM test 
WHERE DATE_FORMAT( FROM_UNIXTIME( regdate ) , '%Y%m%d' ) 
BETWEEN DATE_FORMAT( DATE_ADD( now( ) , INTERVAL '-3' DAY ) , '%Y%m%d' ) 
AND DATE_FORMAT( DATE_ADD( now( ) , INTERVAL '3' DAY ) , '%Y%m%d' ) 

 

// BETWEEN 검색

SELECT DATE_FORMAT( FROM_UNIXTIME( regdate ) , '%Y-%m-%d %H:%i:%s' ) regdate 
FROM test 
WHERE DATE_ADD(FROM_UNIXTIME(regdate), INTERVAL '3' DAY), '%Y%m%d') 
BETWEEN DATE_FORMAT( DATE_ADD( now( ) , INTERVAL '-3' DAY ) , '%Y%m%d' ) 
AND DATE_FORMAT( DATE_ADD( now( ) , INTERVAL '3' DAY ) , '%Y%m%d' )