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 형에는 안들어간다.
MySQL에서의 날짜관련 예제
A날짜에서 B날짜까지의 시간/분/초 구하기
create table date_test(a datetime,b datetime);
insert into date_test values ('2005-03-02 12:00','2005-05-02 12:00');
select sec_to_time(unix_timestamp(b)-unix_timestamp(a)) from date_test;
여러 데이타형으로 날짜 표현하기
*table생성**
create table date1
(
a datetime null,
b timestamp(14) null,
c int null,
d varchar(20) null
);
**date insert**
insert into date1 values(now(),null,unix_timestamp(),now());
insert into date1 values(now(),null,unix_timestamp(),now());
insert into date1 values(now(),null,unix_timestamp(),now());
**table content**
+---------------------+----------------+------------+---------------------+
| a | b | c | d |
+---------------------+----------------+------------+---------------------+
| 2005-01-04 09:32:43 | 20050104093243 | 1104798763 | 2005-01-04 10:57:25 |
| 2005-01-04 09:32:50 | 20050104093250 | 1104798770 | 2005-01-04 10:57:25 |
| 2005-01-04 09:32:51 | 20050104093251 | 1104798771 | 2005-01-04 10:57:25 |
+---------------------+----------------+------------+---------------------|
** datetime형 날짜검색 방법 **
where wr_datetime between (now() - interval 2 day) and now()
**날짜 검색**
select * from date1 where a between '2006-01-01' and '2006-05-04';
//a는 datetime형이며 2005-01-01와 2005-01-04는 00:00:00이 생략되어있다.
select * from date1 where b between '2006-01-01' and '2006-06-04';
//b는 timestamp형이며 위와 같이 00:00:00이 생략되어있어 있다.
select * from date1 where from_unixtime(c) between '2006-01-01' and '2007-01-04';
//c는 int형이며 unix timestamp값을 가지고있다. datetime형식으로 변환하여 검색가능
select * from date1 where from_unixtime(c) not between '2005-01-01' and '2005-01-04';
//c는 int형이며 unix timestamp값을 가지고있다. datetime형식으로 변환하여 검색가능
//not을 붙여서 a and b에 포함하지 않는 것만 검색
select * from date1 where d between '2006-01-01' and '2007-01-05';
//d는 varchar로 datetime형식이다. between으로 날짜 검색이 가능하다.
※ 날짜 검색시 날짜형식을 2005-1-9과 2005-01-09는 datetime형이나 timestamp형에서는 상관없으나 int,varchar에서는 크기와 대소문자를 비교하므로 2005-01-09형식으로 해야 합니다.
select (a - interval 1 day) as lee from date1;
--된다.
select (b - interval 1 day) as lee from date1;
--안된다.
select (c - interval 1 day) as lee from date1;
--된다.
select (d - interval 1 day) as lee from date1;
--안된다.
//1일전출력
select (a - interval 1 month) as lee from date1;
//datetime형,int,varchar에만 사용하며 한달전 출력
//hour_second_minute_year_day_month 복합적으로 사용이 불가능합니다.
** varchar 형은 직접적으로 연산을 하면 안됩니다.
** datetime,int,timestamp형은 직접연산(+)을 할시 초로 적용됩니다.
날짜, 시간을 KST 한국표준시가 아닌 PST 태평양표준시로 출력하기
select (now() -interval 17 hour);
-- PST는 KST보다 17시간 느림
출력 : 2007-05-27 07:00:24
select date_format((now() -interval 17 hour),'%Y%m%d ');
출력 : 20070527
select date_format((now() -interval 17 hour),'%H%i%s');
출력 : 070024
varchar 데이타형에 날짜형식(20080101)으로 들어가있을때 일자 연장시키는 쿼리
update g4_member set mb_2 = replace((mb_2+interval 12 day),'-','') where mb_level = 3;
update g4_member set mb_2 = replace((mb_2+interval 7 day),'-','') where mb_level = 3 and mb_2 >= date_format(now(),'%Y%m%d');
update g4_member set mb_2 = replace((mb_2+interval (dayofmonth(current_date())-dayofmonth('2008-12-11')) day),'-','') where mb_level = 3 and mb_2 >= date_format(now(),'%Y%m%d');
-- 위의 쿼리는 사용하지 말자
update g4_member set mb_2 = replace((date_format(mb_datetime,'%Y%m%d')+interval 31 day),'-','') where mb_level = 3;
update g4_member set mb_2 = replace((mb_2+interval 6 day),'-','') where mb_level = 3 and mb_datetime < '2009-01-18' and mb_2 >= date_format(now(),'%Y%m%d');
