[Mysql] DATETIME과 TIMESTAMP

- 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 형에는 안들어간다.

 

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');

-- 정회원 가입일자가 오늘전날 기간이 오늘이후인 사람대상

출처 : http://www.zetblog.net/?0008320107