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 타입의 날짜 뽑기
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
덧글