1. HOME
  2. [종료]구루비 DB 스터디
  3. 2012년 하반기 - 오라클 성능 트러블슈팅의 기초 스터디
  4. Date, Timestamp, 그리고 Interval 타입

Date, Timestamp, 그리고 Interval 타입 (by openlsw) [2012.11.16]

#Date, Timestamp, 그리고 Interval 타입

  • DATE + (소수점 이하 초 단위 표현 + TIME ZONE 지원) = TIMESTAMP : 일시 정보
  • INTERVAL : 기간 정보
○ 날짜형식(YYYY-MM-DD)
  • 데이터베이스의 DATE, TIMESTAMP, INTERVAL 형식 과 문자열 사이의 상호 변환에 사용됨
  • 기본 날짜형식에 의존하지 말고 명시적인 날짜형식을 사용해야 함 (01-02-03 과 같은 모호성)
SQL설명
{code:noneborderStyle=solid}INSERT INTO T (DATE_COLUMN) VALUES ('01/02/03');{code}{code:noneborderStyle=none}암시적인 날짜형식(DD/MM/YY)에 의존, 기본 날짜형식이 바뀐다면?{code}
{code:noneborderStyle=solid}INSERT INTO T (DATE_COLUMN) VALUES (TO_DATE('01/02/03', 'DD/MM/YY'));{code}{code:noneborderStyle=none}명시적인 날짜형식 적용, 하지만 연도 정의가 모호함(1903년 OR 2003년){code}
{code:noneborderStyle=solid}INSERT INTO T (DATE_COLUMN) VALUES (TO_DATE('01/02/2003', 'DD/MM/YYYY'));{code}{code:noneborderStyle=none}GOOD!{code}
○ DATE 타입
  • 고정 길이 7 바이트, 세기/연도/월/일/시/분/초 속성을 가짐
Demo#1 (DATE 타입의 실제 저장 정보)
{code:sqlborderStyle=solid}
15:44:55 SCOTT@ORCL > CREATE TABLE T (X DATE);

Table created.

15:44:58 SCOTT@ORCL > INSERT INTO T (X) VALUES (TO_DATE('25-JUN-2005 12:01:00', 'DD-MON-YYYY HH24:MI:SS'));

1 row created.

15:45:21 SCOTT@ORCL > SELECT X, DUMP(X, 10) D FROM T;

X D

-
-
25-JUN-05 Typ=12 Len=7: 120,105,6,25,13,2,1

-- 연도 바이트[120,105]는 100을 추가하여 저장 : 연도 바이트 에서 100을 뺐을때 음수면 B.C(기원전), 양수면 A.D(기원후)
-- 월, 일 바이트[6,25]는 가공 없이 저장
-- 시, 분, 초 바이트는 실제 값에서 각각 1을 더해서 저장
15:45:28 SCOTT@ORCL > INSERT INTO T (X) VALUES (TO_DATE('01-JAN-4712BC', 'DD-MON-YYYYBC HH24:MI:SS'));

1 row created.

15:56:24 SCOTT@ORCL > INSERT INTO T (X) VALUES (TO_DATE('01-JAN-4710BC', 'DD-MON-YYYYBC HH24:MI:SS'));

1 row created.

15:53:49 SCOTT@ORCL > SELECT X, DUMP(X, 10) D FROM T;

X D

-
-
25-JUN-05 Typ=12 Len=7: 120,105,6,25,13,2,1
01-JAN-12 Typ=12 Len=7: 53,88,1,1,1,1,1
01-JAN-10 Typ=12 Len=7: 53,90,1,1,1,1,1

– DATE 타입의 저장 포멧은 자연적으로 정렬됨

|

||Demo#2 (DATE 타입 자르기, 쉽다)||
|{code:sql|borderStyle=solid}
16:53:06 SCOTT@ORCL > CREATE TABLE T (WHAT VARCHAR2(10), X DATE);

Table created.

16:53:20 SCOTT@ORCL > INSERT INTO T (WHAT, X) VALUES ('ORIG', TO_DATE( '25-JUN-2005 12:01:00', 'DD-MON-YYYY HH24:MI:SS'));

1 row created.

SCOTT@ORCL > INSERT INTO T (WHAT, X)
SELECT 'MINUTE', TRUNC(X, 'MI') FROM T
UNION ALL
SELECT 'DAY', TRUNC(X, 'DD') FROM T
UNION ALL
SELECT 'MONTH', TRUNC(X, 'MM') FROM T
UNION ALL
SELECT 'YEAR', TRUNC(X, 'Y') FROM T;  2    3    4    5    6    7    8 

4 rows created.

16:53:47 SCOTT@ORCL > SELECT WHAT, X, DUMP(X, 10) D FROM T;

WHAT       X            D
---------- ------------ ----------------------------------
ORIG       25-JUN-05    Typ=12 Len=7: 120,105,6,25,13,2,1
MINUTE     25-JUN-05    Typ=12 Len=7: 120,105,6,25,13,2,1
DAY        25-JUN-05    Typ=12 Len=7: 120,105,6,25,1,1,1
MONTH      01-JUN-05    Typ=12 Len=7: 120,105,6,1,1,1,1
YEAR       01-JAN-05    Typ=12 Len=7: 120,105,1,1,1,1,1

-- DATE 타입을 다른 포멧으로 변환하지 않고 쉽게 끝을 잘라낼 수 있다

|

  • DATE 타입에서 시간을 더하거나 빼기
  • 시, 분, 초 연산은 NUMTODSINTERVAL 함수 사용
  • 일 연산은 숫자 사용
  • 월, 연 연산은 ADD_MONTHS 함수 사용
시간단위연산설명
{code:noneborderStyle=solid}N 초{code}{code:noneborderStyle=none}DATE + N / 24 / 60 / 60
DATE + NUMTODSINTERVAL(N, 'SECOND'){code}		{code:noneborderStyle=none}1일 = 24시간 * 60분 * 60초 = 86400초{code}
{code:noneborderStyle=solid}N 분{code}{code:noneborderStyle=none}DATE + N / 24 / 60
DATE + NUMTODSINTERVAL(N, 'MINUTE'){code}		{code:noneborderStyle=none}1일 = 24시간 * 60분 = 1440분{code}
{code:noneborderStyle=solid}N 시간{code}{code:noneborderStyle=none}DATE + N / 24
DATE + NUMTODSINTERVAL(N, 'HOUR'){code}		{code:noneborderStyle=none}1일 = 24시간{code}
{code:noneborderStyle=solid}N 일{code}{code:noneborderStyle=none}DATE + N{code}{code:noneborderStyle=none}DATE + N{code}
{code:noneborderStyle=solid}N 주{code}{code:noneborderStyle=none}DATE + (7 * N){code}{code:noneborderStyle=none}1주 = 7일{code}
{code:noneborderStyle=solid}N 월{code}{code:noneborderStyle=none}ADD_MONTHS(DATE, N)
DATE + NUMTOYMINTERVAL(N, 'MONTH'){code}		{code:noneborderStyle=none}월말일자 연산이 필요하다면 ADD_MONTHS{code}
{code:noneborderStyle=solid}N 년{code}{code:noneborderStyle=none}ADD_MONTHS(DATE, 12 * N)
DATE + NUMTOYMINTERVAL(N, 'YEAR'){code}		{code:noneborderStyle=none}NUMTOYMINTERVAL 과 윤일의 만남 = 런타임에러{code}
Demo#3 (월말일자)
{code:sqlborderStyle=solid}
SCOTT@ORCL > ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

Session altered.

– ADD_MONTHS
SCOTT@ORCL > SELECT DT, ADD_MONTHS(DT, 1) FROM (SELECT TO_DATE('29-FEB-2000', 'DD-MON-YYYY') DT FROM DUAL);

DT ADD_MONTHS(DT,1)

---
---
29-FEB-2000 00:00:00 31-MAR-2000 00:00:00

SCOTT@ORCL > SELECT DT, ADD_MONTHS(DT, 1) FROM (SELECT TO_DATE('28-FEB-2001', 'DD-MON-YYYY') DT FROM DUAL);

DT ADD_MONTHS(DT,1)

---
---
28-FEB-2001 00:00:00 31-MAR-2001 00:00:00

SCOTT@ORCL > SELECT DT, ADD_MONTHS(DT, 1) FROM (SELECT TO_DATE('30-JAN-2000', 'DD-MON-YYYY') DT FROM DUAL);

DT ADD_MONTHS(DT,1)

---
---
30-JAN-2000 00:00:00 29-FEB-2000 00:00:00

SCOTT@ORCL > SELECT DT, ADD_MONTHS(DT, 1) FROM (SELECT TO_DATE('30-JAN-2001', 'DD-MON-YYYY') DT FROM DUAL);

DT ADD_MONTHS(DT,1)

---
---
30-JAN-2001 00:00:00 28-FEB-2001 00:00:00

– NUMTOYMINTERVAL
SCOTT@ORCL > ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

Session altered.

SCOTT@ORCL > SELECT DT, DT + NUMTOYMINTERVAL(1, 'MONTH') FROM (SELECT TO_DATE('29-FEB-2000', 'DD-MON-YYYY') DT FROM DUAL);

DT DT+NUMTOYMINTERVAL(1,'M

---
---
29-FEB-2000 00:00:00 29-MAR-2000 00:00:00

SCOTT@ORCL > SELECT DT, DT + NUMTOYMINTERVAL(1, 'MONTH') FROM (SELECT TO_DATE('28-FEB-2001', 'DD-MON-YYYY') DT FROM DUAL);

DT DT+NUMTOYMINTERVAL(1,'M

---
---
28-FEB-2001 00:00:00 28-MAR-2001 00:00:00

SCOTT@ORCL > SELECT DT, DT + NUMTOYMINTERVAL(1, 'MONTH') FROM (SELECT TO_DATE('30-JAN-2000', 'DD-MON-YYYY') DT FROM DUAL);
SELECT DT, DT + NUMTOYMINTERVAL(1, 'MONTH') FROM (SELECT TO_DATE('30-JAN-2000', 'DD-MON-YYYY') DT FROM DUAL)
*
ERROR at line 1:
ORA-01839: date not valid for month specified – 2000.02.30 은 없음

SCOTT@ORCL > SELECT DT, DT + NUMTOYMINTERVAL(1, 'MONTH') FROM (SELECT TO_DATE('30-JAN-2001', 'DD-MON-YYYY') DT FROM DUAL);
SELECT DT, DT + NUMTOYMINTERVAL(1, 'MONTH') FROM (SELECT TO_DATE('30-JAN-2001', 'DD-MON-YYYY') DT FROM DUAL)
*
ERROR at line 1:
ORA-01839: date not valid for month specified – 2001.02.30 은 없음

|

 * 두 DATE 간의 차이 구하기
   * 뺄셈
   * MONTHS_BETWEEN 사용
   * NUMTODSINTERVAL, NUMTOYMINTERVAL 사용
  
||Demo#4 (DATE 간의 차이)||
|{code:sql|borderStyle=solid}
SCOTT@ORCL > SELECT DT2 - DT1
  2         , MONTHS_BETWEEN(DT2, DT1) MONTHS_BTWN
            , NUMTODSINTERVAL(DT2 - DT1, 'DAY') DAYS
            , NUMTOYMINTERVAL(TRUNC(MONTHS_BETWEEN(DT2, DT1)), 'MONTH') MONTHS
         FROM (SELECT TO_DATE('29-FEB-2000 01:02:03', 'DD-MON-YYYY HH24:MI:SS') DT1,
                      TO_DATE('15-MAR-2001 11:22:33', 'DD-MON-YYYY HH24:MI:SS') DT2
                 FROM DUAL);  3    4    5    6    7  

   DT2-DT1 MONTHS_BTWN DAYS                          MONTHS
---------- ----------- ----------------------------- -------------
380.430903  12.5622872 +000000380 10:20:30.000000000 +000000001-00  

SCOTT@ORCL > SELECT NUMTOYMINTERVAL(TRUNC(MONTHS_BETWEEN(DT2, DT1)), 'MONTH') YEARS_MONTHS,
  2                  NUMTODSINTERVAL(DT2 - ADD_MONTHS( DT1, TRUNC(MONTHS_BETWEEN(DT2, DT1)) ), 'DAY') DAYS_HOURS
                FROM (SELECT TO_DATE('29-FEB-2000 01:02:03', 'DD-MON-YYYY HH24:MI:SS') DT1,
                             TO_DATE('15-MAR-2001 11:22:33', 'DD-MON-YYYY HH24:MI:SS') DT2
                        FROM DUAL);  3    4    5  

YEARS_MONTHS   DAYS_HOURS
-------------- ------------------------------
+000000001-00  +000000015 10:20:30.000000000

|

○ TIMESTAMP 타입
Demo#4 (TIMESTAMP 타입 속성)
{code:sqlborderStyle=solid}
– TIMESTAMP(0)
SCOTT@ORCL > CREATE TABLE T
( DT DATE,
TS TIMESTAMP(0)
);
2 3 4
Table created.

SCOTT@ORCL > INSERT INTO T VALUES (SYSDATE, SYSTIMESTAMP);

1 row created.

SCOTT@ORCL > SELECT DUMP(DT, 10) DUMP, DUMP(TS, 10) DUMP FROM T;

DUMP DUMP

-
Typ=12 Len=7: 120,112,11,16,20,59,1 Typ=180 Len=7: 120,112,11,16,20,59,1
-- 데이터 타입(Typ: 12 / 180)은 다르지만 길이(Len) 와 값은 같다

– TIMESTAMP(9)
SCOTT@ORCL > CREATE TABLE T
( DT DATE,
TS TIMESTAMP(9)
); 2 3 4

Table created.

SCOTT@ORCL > INSERT INTO T VALUES (SYSDATE, SYSTIMESTAMP);

1 row created.

SCOTT@ORCL > SELECT DUMP(DT, 10) DUMP, DUMP(TS, 10) DUMP FROM T;

DUMP DUMP

---
Typ=12 Len=7: 120,112,11,16,21,2,35 Typ=180 Len=11: 120,112,11,16,21,2,35,22,91,91,176
-- 소수점 이하 초단위 표현을 위해 4 바이트를 추가로 사용 한듯

SCOTT@ORCL > ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

Session altered.

SCOTT@ORCL > SELECT * FROM T;

DT TS

---

16-NOV-2012 20:01:34 16-NOV-12 08.01.34.375086000 PM

SCOTT@ORCL > SELECT DUMP(TS, 16) DUMP FROM T;

DUMP

---
Typ=180 Len=11: 78,70,b,10,15,2,23,16,5b,5b,b0

SCOTT@ORCL > SELECT TO_NUMBER('165b5bb0', 'XXXXXXXX') FROM DUAL;

TO_NUMBER('165B5BB0','XXXXXXXX')


375086000 -- 했네

|

||Demo#5 (TIMESTAMP 덧셈 뺄셈)||
|{code:sql|borderStyle=solid}
SCOTT@ORCL > ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

Session altered.

SCOTT@ORCL > SELECT SYSTIMESTAMP TS, SYSTIMESTAMP + 1 DT FROM DUAL;

TS                                   DT
------------------------------------ ---------------------
16-NOV-12 08.16.53.232722 PM +09:00  17-NOV-2012 20:16:53 -- 묵시적 변환 (DATE)

SCOTT@ORCL > SELECT SYSTIMESTAMP TS, SYSTIMESTAMP + NUMTODSINTERVAL(1, 'DAY') DT FROM DUAL;

TS                                   DT
------------------------------------ ---------------------------------------
16-NOV-12 08.17.03.456233 PM +09:00  17-NOV-12 08.17.03.456233000 PM +09:00

|

Demo#6 (TIMESTAMP 차이 구하기)
{code:sqlborderStyle=solid}
– DATE - DATE : 숫자
– TIMESTAMP - TIMESTAMP : INTERVAL

SCOTT@ORCL > SELECT DT2 - DT1
FROM (SELECT TO_TIMESTAMP('29-FEB-2000 01:02:03.122000', 'DD-MON-YYYY HH24:MI:SS.FF') DT1,
TO_TIMESTAMP('15-MAR-2001 11:22:33.000000', 'DD-MON-YYYY HH24:MI:SS.FF') DT2
FROM DUAL); 2 3 4

DT2-DT1

--
+000000380 10:20:29.878000000

SCOTT@ORCL > SELECT NUMTOYMINTERVAL(TRUNC(MONTHS_BETWEEN(DT2, DT1)), 'MONTH') YEARS_MONTHS
2 , DT2 - ADD_MONTHS(DT1, TRUNC(MONTHS_BETWEEN(DT2 ,DT1))) DAYS_HOURS
FROM (SELECT TO_TIMESTAMP('29-FEB-2000 01:02:03.122000', 'DD-MON-YYYY HH24:MI:SS.FF') DT1,
TO_TIMESTAMP('15-MAR-2001 11:22:33.000000', 'DD-MON-YYYY HH24:MI:SS.FF') DT2
FROM DUAL); 3 4 5

YEARS_MONTHS DAYS_HOURS

--
--
+000000001-00 +000000015 10:20:30.000000000

SCOTT@ORCL > SELECT NUMTOYMINTERVAL(TRUNC(MONTHS_BETWEEN(DT2, DT1)), 'MONTH') YEARS_MONTHS
2 , DT2 - (DT1 + NUMTOYMINTERVAL(TRUNC(MONTHS_BETWEEN(DT2 ,DT1)), 'MONTH')) DAYS_HOURS
FROM (SELECT TO_TIMESTAMP('29-FEB-2000 01:02:03.122000', 'DD-MON-YYYY HH24:MI:SS.FF') DT1,
TO_TIMESTAMP('15-MAR-2001 11:22:33.000000', 'DD-MON-YYYY HH24:MI:SS.FF') DT2
FROM DUAL); 3 4 5

, DT2 - (DT1 + NUMTOYMINTERVAL( TRUNC(MONTHS_BETWEEN(DT2 ,DT1)), 'MONTH' )) DAYS_HOURS
*
ERROR at line 2:
ORA-01839: date not valid for month specified – TRUNC(MONTHS_BETWEEN(DT2 ,DT1)) 가 12 이므로 2001.02.29 를 찾게 되는데... 그런 날짜는 없음 ㅋ

SCOTT@ORCL > SELECT NUMTOYMINTERVAL(TRUNC(MONTHS_BETWEEN(DT2, DT1)), 'MONTH') YEARS_MONTHS
2 , DT2 - (DT1 + NUMTOYMINTERVAL(TRUNC(MONTHS_BETWEEN(DT2 ,DT1)), 'MONTH')) DAYS_HOURS
FROM (SELECT TO_TIMESTAMP('29-FEB-2000 01:02:03.122000', 'DD-MON-YYYY HH24:MI:SS.FF') DT1,
TO_TIMESTAMP('15-APR-2001 11:22:33.000000', 'DD-MON-YYYY HH24:MI:SS.FF') DT2
FROM DUAL); 3 4 5

YEARS_MONTHS DAYS_HOURS

--
--
+000000001-01 +000000017 10:20:29.878000000

|

||Demo#7 (TIMESTAMP WITH TIME ZONE)||
|{code:sql|borderStyle=solid}
-- TIMESTAMP / TIMESTAMP WITH TIME ZONE 비교
SCOTT@ORCL > CREATE TABLE T
( TS    TIMESTAMP,
  TS_TZ TIMESTAMP WITH TIME ZONE
);  2    3    4  

Table created.

SCOTT@ORCL > INSERT INTO T (TS, TS_TZ) VALUES (SYSTIMESTAMP, SYSTIMESTAMP);

1 row created.

SCOTT@ORCL > SELECT * FROM T;

TS                            TS_TZ
----------------------------- ------------------------------------
16-NOV-12 08.51.02.884882 PM  16-NOV-12 08.51.02.884882 PM +09:00

SCOTT@ORCL > SELECT DUMP(TS) DUMP, DUMP(TS_TZ) DUMP FROM T;


DUMP                                                DUMP
--------------------------------------------------- ---------------------------------------------------------
Typ=180 Len=11: 120,112,11,16,21,52,3,52,190,58,80  Typ=181 Len=13: 120,112,11,16,12,52,3,52,190,58,80,29,60

-- 보이는것 : 2012-11-16 20:51:02
-- 저장된것 : 2012-11-16 20:51:02 (TIMESTAMP)
-- 저장된것 : 2012-11-16 11:51:02 (TIMESTAMP WITH TIME ZONE)

-- 서울 : GMT +09:00

-- TIMESTAMP WITH TIME ZONE 차이 구하기
SCOTT@ORCL > CREATE TABLE T
( TS1 TIMESTAMP WITH TIME ZONE,
  TS2 TIMESTAMP WITH TIME ZONE
);  2    3    4  

Table created.

SCOTT@ORCL > INSERT INTO T (TS1, TS2) VALUES ( TIMESTAMP'2010-02-27 16:02:32.212 US/Eastern', TIMESTAMP'2010-02-27 16:02:32.212 US/Pacific');

1 row created.

SCOTT@ORCL > SELECT TS1 - TS2 FROM T;

TS1-TS2
---------------------------
-000000000 03:00:00.000000

-- US/Pacific : 태평양표준시 GMT -08:00
-- US/Eastern : 동부표준시   GMT -05:00

|

Demo#8 (TIMESTAMP WITH LOCAL TIME ZONE)
{code:sqlborderStyle=solid}
SCOTT@ORCL > CREATE TABLE T
( DT DATE,
TS1 TIMESTAMP WITH TIME ZONE,
TS2 TIMESTAMP WITH LOCAL TIME ZONE
); 2 3 4 5

Table created.

SCOTT@ORCL > INSERT INTO T (DT, TS1, TS2)
VALUES ( TIMESTAMP'2010-02-27 16:02:32.212 US/PACIFIC',
TIMESTAMP'2010-02-27 16:02:32.212 US/PACIFIC',
TIMESTAMP'2010-02-27 16:02:32.212 US/PACIFIC'); 2 3 4

1 row created.

SCOTT@ORCL > SELECT DBTIMEZONE FROM DUAL;

DBTIME

--
+00:00

SCOTT@ORCL > SELECT DUMP(DT), DUMP(TS1), DUMP(TS2) FROM T;


DUMP(DT) Typ=12 Len=7: 120,110,2,27,17,3,33 -- 2010-02-27 16:02:32
DUMP(TS1) Typ=181 Len=13: 120,110,2,28,1 ,3,33,12,162,221,0,137,156 -- 2010-02-28 00:02:32 (8시간+)
DUMP(TS2) Typ=231 Len=11: 120,110,2,28,1 ,3,33,12,162,221,0 -- 2010-02-28 00:02:32 (8시간+)

SCOTT@ORCL > SELECT TS1, TS2 FROM T;

TS1 TS2

-
27-FEB-10 04.02.32.212000 PM US/PACIFIC 28-FEB-10 09.02.32.212000 AM – 9시간+

SCOTT@ORCL > DELETE FROM T;

1 row deleted.

SCOTT@ORCL > INSERT INTO T (DT, TS1, TS2)
VALUES ( TIMESTAMP'2010-04-12 16:02:32.212 US/PACIFIC',
TIMESTAMP'2010-04-12 16:02:32.212 US/PACIFIC',
TIMESTAMP'2010-04-12 16:02:32.212 US/PACIFIC'); 2 3 4

1 row created.

SCOTT@ORCL > SELECT TS1, TS2 FROM T;

TS1 TS2

-
12-APR-10 04.02.32.212000 PM US/PACIFIC 13-APR-10 08.02.32.212000 AM

SCOTT@ORCL > ALTER DATABASE SET TIME_ZONE = 'PST';
ALTER DATABASE SET TIME_ZONE = 'PST'
*
ERROR at line 1:
ORA-30079: cannot alter database timezone when database has TIMESTAMP WITH LOCAL TIME ZONE columns
– TIMESTAMP WITH LOCAL TIME ZONE 컬럼의 데이터를 모두 바꿀 수 없기에...

|

h6. ○ INTERVAL 타입

||Demo#9 (INTERVAL)||
|{code:sql|borderStyle=solid}
SCOTT@ORCL > SELECT DT2 - DT1
                FROM (SELECT TO_TIMESTAMP('29-FEB-2000 01:02:03.122000', 'DD-MON-YYYY HH24:MI:SS.FF') DT1,
                             TO_TIMESTAMP('15-MAR-2001 11:22:33.000000', 'DD-MON-YYYY HH24:MI:SS.FF') DT2
                        FROM DUAL);  2    3    4  

DT2-DT1
---------------------------------------------------------------------------
+000000380 10:20:29.878000000

SCOTT@ORCL > SELECT EXTRACT( DAY    FROM DT2 - DT1) DAY,
           2         EXTRACT( HOUR   FROM DT2 - DT1) HOUR,
                     EXTRACT( MINUTE FROM DT2 - DT1) MINUTE,
                     EXTRACT( SECOND FROM DT2 - DT1) SECOND
                FROM (SELECT TO_TIMESTAMP('29-FEB-2000 01:02:03.122000', 'DD-MON-YYYY HH24:MI:SS.FF') DT1,
                             TO_TIMESTAMP('15-MAR-2001 11:22:33.000000', 'DD-MON-YYYY HH24:MI:SS.FF') DT2
                        FROM DUAL);  3    4    5    6    7  

       DAY       HOUR     MINUTE     SECOND
---------- ---------- ---------- ----------
       380         10         20     29.878
       
       
-- INTERVAL YEAR(n) TO MONTH       
SCOTT@ORCL > SELECT NUMTOYMINTERVAL(5, 'YEAR') + NUMTOYMINTERVAL(2, 'MONTH') FROM DUAL;

NUMTOYMINTERVAL(5,'YEAR')+NUMTOYMINTERVAL(2,'MONTH')
---------------------------------------------------------------------------
+000000005-02

SCOTT@ORCL > SELECT NUMTOYMINTERVAL(5 * 12 + 2, 'MONTH') FROM DUAL;

NUMTOYMINTERVAL(5*12+2,'MONTH')
---------------------------------------------------------------------------
+000000005-02

SCOTT@ORCL > SELECT TO_YMINTERVAL('5-2') FROM DUAL;

TO_YMINTERVAL('5-2')
---------------------------------------------------------------------------
+000000005-02

SCOTT@ORCL > SELECT INTERVAL '5-2' YEAR TO MONTH FROM DUAL;

INTERVAL'5-2'YEARTOMONTH
---------------------------------------------------------------------------
+05-02

-- INTERVAL DAY(n) TO SECOND(m)
SCOTT@ORCL > SELECT NUMTODSINTERVAL(10, 'DAY') +
                     NUMTODSINTERVAL(2, 'HOUR') +
                     NUMTODSINTERVAL(3, 'MINUTE') +
                     NUMTODSINTERVAL(2.3312, 'SECOND') 
                FROM DUAL;  2    3    4    5  

NUMTODSINTERVAL(10,'DAY')+NUMTODSINTERVAL(2,'HOUR')+NUMTODSINTERVAL(3,'MINU
---------------------------------------------------------------------------
+000000010 02:03:02.331200000

SCOTT@ORCL > SELECT NUMTODSINTERVAL (10*86400 + 2*3600 + 3*60 + 2.3312, 'SECOND') FROM DUAL;

NUMTODSINTERVAL(10*86400+2*3600+3*60+2.3312,'SECOND')
---------------------------------------------------------------------------
+000000010 02:03:02.331200000

SCOTT@ORCL > SELECT TO_DSINTERVAL('10 02:03:02.3312') FROM DUAL;

TO_DSINTERVAL('1002:03:02.3312')
---------------------------------------------------------------------------
+000000010 02:03:02.331200000

SCOTT@ORCL > SELECT INTERVAL '10 02:03:02.3312' DAY TO SECOND FROM DUAL;

INTERVAL'1002:03:02.3312'DAYTOSECOND
---------------------------------------------------------------------------
+10 02:03:02.331200

|

  1. HOME
  2. [종료]구루비 DB 스터디
  3. 2012년 하반기 - 오라클 성능 트러블슈팅의 기초 스터디
  4. Date, Timestamp, 그리고 Interval 타입