[MySQL] 날짜시간 변환
1) Unixtime -> Date 형식(일반적인 시간)으로 변환
SELECT user_id, FROM_UNIXTIME(`regdate`,'%Y%m%d %H%i%s') FROM member
: from_unixtime 이라는 MySQL 함수를 이용해서 입력받은 Unixtime 값을 일반적으로 사용하는 시간으로 변환한다. 이때 사용하는 "%" 값들은 PHP나 기타 프로그램에서 사용하는 시간 표현 문자와 동일하다. (PHP 의 date 함수 설명)
2) 현재 시간을 Unixtime으로 구하기
SELECT UNIX_TIMESTAMP()
3) 지정한 Date형식(일반적인 시간) ->Unixtime 으로 변환
SELECT UNIX_TIMESTAMP('2009-05-15 20:11:22')
: 이 때 입력하는 시간은 '년-월-일' 만 입력해도 된다.
4) 시간 더하기 & 빼기
SELECT DATE_ADD(now(), INTERVAL 1 day))
: 현재 시간에서 1일을 더한다.
SELECT DATE_ADD(now(), INTERVAL -1 day))
: 현재 시간에서 1일을 뺀다.
* day 부분에 들어가는 unit은...
- year : 년
- month : 월
- day : 일
- hour : 시간
- minute : 분
- secont : 초
5) 원하는 형태로 보기
SELECT DATE_FORMAT(regdate, "%Y%m%d %H%i%s") FROM users
: regdate 시간 필드를 20000526 102012 형식의 시간으로 표시한다.
이 외에도 아래과 같은 많은 시간 관련 함수가 존재한다.
(자세한 내용은 http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html 참고)
DAYOFWEEK(date) : 요일을 정수로 반환(1=일요일, 7=토요일)
DAYOFMONTH(date): 월의 몇번째 날인지를 정수로 반환
DAYOFYEAR(date) : 년중 몇번째 날인지를 정수로 반환
MONTH(date) : 월을 1∼12의 정수로 반환
DAYNAME(date) : 요일 이름을 문자로 반환
MONTHNAME(date) : 월 이름을 문자로 반환
YEAR(date) : 년도를 4자리 정수로 반환
나머지도 응용하면 이용이 가능할 것이다.
출처 : http://blog.bsmind.co.kr/44
또 따른 내용
- UNIX_TIMESTAMP()함수 사용해서 long 타입의 날짜 뽑기
select UNIX_TIMESTAMP(필드명) from 테이블명
- DATETIME은 한 테이블에 여러개 존재 가능 vs TIMESTAMP 는 한 테이블에 하나만 가능
- DATETIME은 직접 insert 가능 vs TIMESTAMP 는 한 개의 Row 생성 시 자동 생성 됨.
----------------------------------------------------------------------------------------------------------
MySQL 날짜/시간 데이타형
time type
(23:01:01등의 시간:분:초를 저장 컬럼-3바이트)
date type
(1000-01-01부터 9999-12-31 저장하는 날짜저장 컬럼-3바이트)
year type
(1970~2069년도를 저장-1바이트)
datetime type
(1000-01-01 00:00:00부터 9999-12-31 23:59:59저장하는 날짜저장 컬럼-8바이트)
timestamp type-4바이트
mysql에서는 timestamp형은 새로운열이나 insert나 해당열에 update시에 자동으로 입력되어지는 날짜형이다. 입력되어지는 날짜형식은 20060301133638 와 같이 -와 :가 없이 붙어서 입력된다.
※ timestamp값은 1970년01월01일 00:00:00 이후부터 현재까지 지난기간을 초를 환산하여 표현한 값입니다.
MySQL에서의 날짜 관련 데이타형 선언시 DEFAULT 연산자 사용
정상문법
create table date_test(a date default '0000-00-00');
create table date_test(a datetime default '0000-00-00 00:00:00');
에러문법
create table date_test(a date default curdate());
MySQL 날짜/시간 내장함수
now(),sysdate()
현재시간및날짜를 반환한다.(DATETIME형으로 출력)
ex)2006-01-10 13:56:16 (시간은 24시간제로 출력)
curdate()
현재날자를 반환한다.(DATE형으로 출력)
ex - 2006-01-10
curtime()
현재시간을 반환한다.-(TIME형으로 출력)
ex - 13:59:16
unix_timestamp();
현재시간을 unix timestamp형식(초)로 환산하여 출력한다.-int형,varchr형에 대입
ex)1136869199
※ unix timestamp형식은 보통 여러 dbms들간의 날짜 호환성을 위해서 사용한다.
이말은 보통 dbms에서의 날짜표현 포맷은 yy-mm-dd hh:mm:ss 이지만 이러한 포맷형식을
따르지 않는 dbms라면 unix timestamp형식으로 변환후 해당 dbms의 고유한 포맷에 맞게
변환할수 있기 때문이다.
from_unixtime(날짜);
unix_timestamp로부터 현재의 날짜를 읽기쉬운 YYYY-MM-DD HH:MM:SS형태로 리턴
ex)2006-01-10 13:59:59
date_format(날짜,시간,포맷);
시스템형식으로 저장된 날짜+시간, 날짜, 시간을 원하는 포맷으로 변경후 출력한다.
date_format함수 사용예제(문자형으로 출력)
select date_format(now(),'%Y/%m/%d %H:%i:%s');
-- 2006/07/03 15:14:19 (시간을 24시간제로 출력)
select date_format(now(),'%Y/%m/%d %h:%i:%s');
-- 2006/07/03 03:14:19 (시간을 12시간제로 출력)
select date_format(curdate(),'%Y/%m/%d %H:%i:%s');
-- 2006/07/03 00:00:00
select date_format(curdate(),'%y/%m/%d');
-- 06/07/03
MySQL에서의 날짜/시간 관련 쿼리
varchar 타입에는 timestamp형,date형이 들어가며 between 예약어를 사용한 쿼리도 사용
할수 있다. = 연산자는 검색이 된다.
timestamp 타입에는 now()을 넣으면 -와 :을 제외한 값이 들어간다.
timestamp 타입에는 unix_timestamp 형식은 제대로 들어가지 않는다.
INT 타입에는 unix_timestamp 형식은 제대로 들어가지 않는다.
unix_timestamp 형식은 INT형이나 VARCHAR형에 넣어야 한다.
DATE, DATETIME 형에는 안들어간다.
댓글
댓글 쓰기