 |
난이도 : 초급
Paul Yip, Database Consultant, IBM Toronto Lab
2003 년 8 월 28 일
Windows, UNIX, 리눅스 플랫폼용 DB2 Universal Database에서 SQL을 사용하여 날짜, 시간, 타임스탬프를 조작하는 방법을 설명한다.
© 2002 International Business Machines Corporation. 저작권 소유.
알림:이 글을 읽기 전에 디스클레이머를 읽기 바란다.
IBM® DB2® Universal DatabaseTM에 관한 글이다.
머리말
DB2 UDB에 익숙하지 않은 사람들에게 날짜와 시간을 조작하는 방법을 설명한다. 다른 데이터베이스를 다뤄본 많은 사람들에게 DB2 UDB가 얼마나 쉬운지를 설명하겠다.
기초
SQL을 사용하여 날짜, 시간, 타임스탬프를 파악하려면 해당 DB2 레지스터리를 참조하라.
SELECT current date FROM sysibm.sysdummy1 SELECT current time FROM sysibm.sysdummy1 SELECT current timestamp FROM sysibm.sysdummy1 |
sysibm.sysdummy1 테이블은 위에서 설명한 대로 DB2 레지스터의 값을 찾는데 사용할 수 있는 특별한 인메모리(in-memory) 테이블이다. VALUES 키워드를 사용하여 레지스터나 식을 계산할 수 있다. 예를 들어, DB2 Command Line Processor (CLP)에서 다음 SQL 문장에는 비슷한 정보들이 나타난다.
VALUES current date VALUES current time VALUES current timestamp |
남아있는 예제에는 SELECT ... FROM sysibm.sysdummy1을 반복하거나 VALUES 구문을 사용하지 않고 함수 또는 식을 제공하도록 하겠다.
GMT/CUT에 맞춘 현재 시간이나 현재 타임스탬프를 얻으려면 현재 시간 또는 타임스탬프에서 현재 타임존 레지스터를 제거한다.
current time - current timezone current timestamp - current timezone |
날짜 시간, 타임스탬프의 경우, 적절한 함수를 사용하여 년도, 달, 요일, 시간, 분, 초, 마이크로초를 추출할 수 있다.
YEAR (current timestamp) MONTH (current timestamp) DAY (current timestamp) HOUR (current timestamp) MINUTE (current timestamp) SECOND (current timestamp) MICROSECOND (current timestamp) |
타임스탬프와 관계없이 날짜와 시간을 추출하는 것 역시 매우 쉽다.
DATE (current timestamp) TIME (current timestamp) |
영어로도 날짜와 시간 계산을 할 수 있다. :
current date + 1 YEAR current date + 3 YEARS + 2 MONTHS + 15 DAYS current time + 5 HOURS - 3 MINUTES + 10 SECONDS |
두 날짜 간에 날수가 얼마나 되는지를 계산하려면 날짜를 빼면 된다.
days (current date) - days (date('1999-10-22')) |
다음은 마이크로초 부분을 0으로 재설정하고 현재 타임스탬프를 얻는 방법이다.
CURRENT TIMESTAMP - MICROSECOND (current timestamp) MICROSECONDS |
날짜 또는 시간 값을 다른 텍스트와 연결시키려면 그 값을 문자열로 변환해야 한다. CHAR() 함수를 사용한다.
char(current date) char(current time) char(current date + 12 hours) |
문자열을 날짜 또는 시간 값으로 변환하려면 다음과 같이 한다.
TIMESTAMP ('2002-10-20-12.00.00.000000') TIMESTAMP ('2002-10-20 12:00:00') DATE ('2002-10-20') DATE ('10/20/2002') TIME ('12:00:00') TIME ('12.00.00') |
TIMESTAMP(), DATE(), TIME() 함수는 여러 포맷들을 허용한다. 위 포맷은 단순한 예제일 뿐이다. 독자 여러분이 스스로 해 보길 바란다.
|
경고:
DB2 UDB V8.1 SQL Cookbook (Graeme Birchall) 발췌 (http://ourworld.compuserve.com/homepages/Graeme_Birchall).
DATE 함수에서 쿼트를 없애면 어떻게 될까? 함수는 여전히 작동하겠지만 정확하지 않은 DATE가 나올 것이다.
SELECT DATE(2001-09-22) FROM SYSIBM.SYSDUMMY1; |
결과:
위 결과에서 어떻게 2000년 이나 차이가 났을까? DATE 함수가 입력으로 문자열을 취하면 DB2 날짜의 유효 문자로 간주되고, 따라서 이를 변환하는 것이다. 반대로, 입력이 숫자라면 이 함수는 현재 년도(0001-01-01)에서 1을 제한 날 수를 나타내는 것으로 간주한다. 위 쿼리에서 입력은 2001-09-22였고 이것은 (2001-9)-22과 동일하다. 또한 이것은 1970이다. |
날짜 함수
가끔씩, 두 개의 타임스탬프들이 어떻게 차이가 있는지 알아야 한다. 이를 위해 DB2는 TIMESTAMPDIFF()라고 하는 빌트인 함수를 제공한다. 하지만 리턴된 값은 근사값이다. 윤년을 고려하지 않고 한 달을 30일로만 가정하기 때문이다. 다음은 두 날짜들 간 차이를 찾아내는 방법이다.
timestampdiff (<n>, char( timestamp('2002-11-30-00.00.00')- timestamp('2002-11-08-00.00.00'))) |
<n>의 자리에, 다음 값들을 사용하여 시간 단위를 나타낸다.
- 1 = 초의 소수부분
- 2 = 초
- 4 = 분
- 8 = 시간
- 16 = 요일
- 32 = 주
- 64 = 달
- 128 = 분기
- 256 = 년
timestampdiff()를 사용하면 날짜가 비교적 가까이 있을 때 더 정확한 결과가 나온다. 보다 정확한 계산을 원하면 다음을 사용하여 시간 단위로(초 단위로) 차이를 결정한다.
(DAYS(t1) - DAYS(t2)) * 86400 + (MIDNIGHT_SECONDS(t1) - MIDNIGHT_SECONDS(t2)) |
SQL의 사용자 정의 함수를 사용할 수도 있다.
CREATE FUNCTION secondsdiff(t1 TIMESTAMP, t2 TIMESTAMP) RETURNS INT RETURN ( (DAYS(t1) - DAYS(t2)) * 86400 + (MIDNIGHT_SECONDS(t1) - MIDNIGHT_SECONDS(t2)) ) @ |
윤달일 경우 날 수를 결정할 때 쓸 수 있는 유용한 SQL 함수가 있다.
CREATE FUNCTION daysinyear(yr INT) RETURNS INT RETURN (CASE (mod(yr, 400)) WHEN 0 THEN 366 ELSE CASE (mod(yr, 4)) WHEN 0 THEN CASE (mod(yr, 100)) WHEN 0 THEN 365 ELSE 366 END ELSE 365 END END)@ |
마지막으로 다음은 날짜 조작에 쓰이는 빌트인 함수들이다. 여러분의 필요에 맞는 함수를 빠르게 선택하는데 사용하기 바란다. 보다 자세한 정보는 SQL 레퍼런스를 참조하기 바란다.
| SQL 날짜와 시간 함수 |
| DAYNAME |
인자에 요일 이름(예, 금요일)이 포함된 혼합 문자열을 리턴. |
| DAYOFWEEK |
1에서 7까지 정수 값으로 요일을 나타낸다. 여기에서 1은 일요일을 의미한다. |
| DAYOFWEEK_ISO |
1에서 7까지 정수 값으로 요일을 나타낸다. 여기에서 1은 월요일이다. |
| DAYOFYEAR |
1에서 366 까지의 정수로 날(day)을 리턴. |
| DAYS |
날짜를 정수로 리턴. |
| JULIAN_DAY |
January 1, 4712 B.C. (Julian 달력의 시작)부터 인자에 지정된 날짜 값까지 정수로 날수를 나타냄. |
| MIDNIGHT_SECONDS |
0에서 86400 까지의 정수 값으로 자정(midnight)과 인자에서 지정된 시간 값 사이의 초의 값을 나타냄. |
| MONTHNAME |
달의 이름(예를 들어, January)을 포함하고 있는 혼합 문자열 리턴. |
| TIMESTAMP_ISO |
날짜, 시간, 타임스탬프 인자에 기반한 타임스탬프 값을 리턴. |
| TIMESTAMP_FORMAT |
문자 템플릿을 사용하여 인터프리팅 된 문자열에서 타임스탬프를 리턴. |
| TIMESTAMPDIFF |
두 타임스탬프들 간 차이에 기반하여, 첫 번째 인자에서 정의된 유형의 인터벌 수를 리턴. |
| TO_CHAR |
문자 템플릿을 사용하여 포맷된 타임스탬프의 문자 구현을 리턴. TO_CHAR는 VARCHAR_FORMAT의 동의어이다. |
| TO_DATE |
문자 템플릿을 사용하여 인터프리팅 된 문자열에서 타임스탬프를 리턴. TO_DATE는 TIMESTAMP_FORMAT의 동의어이다. |
| WEEK |
1에서 54까지 정수 값으로 주(week)를 리턴함. 주는 Sunday로 시작한다. |
| WEEK_ISO |
1에서 53까지 정수 값으로 주를 리턴함. |
날짜 포맷 변경
날짜 표현에 관한 질문을 자주 받는다. 날짜에 사용되는 기본 포맷은 데이터베이스의 영역(territory) 코드로 결정된다. (이것은 데이터베이스 생성 때 지정될 수 있다.) 예를 들어, 데이터베이스가 territory=US를 사용하여 만들어졌다면 날짜 포맷은 다음과 같을 것이다.
values current date 1 ---------- 05/30/2003 1 record(s) selected. |
말하자면 이것은 MM/DD/YYYY 포맷이다. 이 포맷을 변경하려면 db2 유틸리티 패키지 컬렉션을 다른 날짜 포맷으로 바인딩한다. 다음은 포맷 종류이다.
| DEF |
영역 코드와 관련된 날짜 포맷과 시간 포맷을 사용. |
| EUR |
IBM 유럽 표준의 날짜와 시간 포맷을 사용함. |
| ISO |
International Standards Organization의 날짜와 시간 포맷을 사용. |
| JIS |
Japanese Industrial Standard의 포맷 사용. |
| LOC |
해당 데이터베이스의 영역 코드와 관련된 로컬 형식의 날짜와 시간 포맷을 사용함. |
| USA |
IBM U.S. 표준을 사용함. |
ISO (YYYY-MM-DD)로 디폴트 포맷을 변경하려면 다음과 같이 한다.
- 명령행에서, 현재 디렉토리를
sqllib\bnd로 변경한다.
예:
On Windows: c:\program files\IBM\sqllib\bnd
On UNIX: /home/db2inst1/sqllib/bnd
- SYSADM 권한을 가진 사용자로서 OS 쉘에서 데이터베이스로 연결한다
db2 connect to DBNAME db2 bind @db2ubind.lst datetime ISO blocking all grant public |
(여러분의 상황에 적용할 때에는 데이터베이스 이름과 선호하는 날짜 포맷으로 대체한다.)
이제 데이터베이스가 ISO 날짜 포맷을 사용한다.
values current date 1 ---------- 2003-05-30 1 record(s) selected. |
날짜/시간 포맷 커스터마이징
마지막 예제에서는 DB2가 로컬 포맷으로 날짜를 나타내는 방식을 어떻게 변경하는지를 설명하였다. 하지만 'yyyymmdd'처럼 커스텀 포맷으로 하고싶다면? 최선의 방법은 커스텀 포맷팅 함수를 다시 작성하는 것이다.
UDF:
create function ts_fmt(TS timestamp, fmt varchar(20)) returns varchar(50) return with tmp (dd,mm,yyyy,hh,mi,ss,nnnnnn) as ( select substr( digits (day(TS)),9), substr( digits (month(TS)),9) , rtrim(char(year(TS))) , substr( digits (hour(TS)),9), substr( digits (minute(TS)),9), substr( digits (second(TS)),9), rtrim(char(microsecond(TS))) from sysibm.sysdummy1 ) select case fmt when 'yyyymmdd' then yyyy || mm || dd when 'mm/dd/yyyy' then mm || '/' || dd || '/' || yyyy when 'yyyy/dd/mm hh:mi:ss' then yyyy || '/' || mm || '/' || dd || ' ' || hh || ':' || mi || ':' || ss when 'nnnnnn' then nnnnnn else 'date format ' || coalesce(fmt,' <null> ') || ' not recognized.' end from tmp </null> |
이 함수 코드는 복잡해보인다. 하지만 자세히 살펴보면 매우 단순하면서도 좋은 코드임을 알 수 있다. 우선, common table expression (CTE)를 사용하여 타임스탬프(첫 번째 입력 매개변수)를 개별 컴포넌트에서 분리한다. 그런 다음, 제공된 포맷(두 번째 입력 매개변수)을 검사하고 요청된 포맷과 부분을 사용하여 타임스탬프를 다시 정렬한다. 이 함수는 매우 유연하다. 또 다른 패턴을 추가하려면 원하는 포맷과 함께 WHEN 구문을 붙이면 된다. 예상하지 못했던 패턴이 나타났다면 에러메시지가 리턴된 것이다.
사용 예제:
values ts_fmt(current timestamp,'yyyymmdd') '20030818' values ts_fmt(current timestamp,'asa') 'date format asa not recognized.' |
요약
날짜와 시간에 관련된 질문들에 답이 되었기 바란다. 더 많은 예제들로 글을 업데이트 하겠다. (사실 독자들 덕분에 벌써 세 번씩이나 업데이트 했다.)
감사의 말
Bill Wilkins, DB2 Partner Enablement
Randy Talsma
디스클레이머
이 글에는 샘플 코드가 포함되어 있습니다. IBM은 여러분 모두가 "로열티 " 없이 이 샘플 코드를 사용할 수 있도록 허용하고 있습니다. 하지만 이 샘플 코드는 그대로 제공되며 특정 목적에 맞춰 상용으로 사용해서는 안됩니다. IBM은 이 소프트웨어를 사용함으로써 초래된 어떤 손해에도 책임을 지지 않습니다. IBM은 손실, 영리 또는 데이터, 직접적인 손해, 간접적인 손해, 특별한 손해, 결과적인 손해, 일시적인 손해, 징벌로 인한 손해 등에 책임이 없습니다. IBM이 그와 같은 손실의 가능성을 만들었더라도 소프트웨어의 사용 또는 미사용에서 발생한 손실에는 책임이 없습니다.
필자소개
|
|
|
Paul Yip, 데이터베이스 컨설턴트, IBM 토론토 연구실. | |
