Date, Timestamp, 그리고 Interval 타입

• DATE, TIMESTAMP, INTERVAL 오라클 데이터 타입은 서로 밀접한 관계를 가진다
• DATE와 TIMESTAMP 는 다양한 정밀도를 가진 DATE/TIME으로 저장
• INTERVAL은 8시간 또는 30일과 같은 시간의 양으로 쉽게 저장

날짜형식

• 원하는 날짜형식으로 데이터베이스의 데이터를 변경
• 입력 문자열을 DATE, TIMESTAMP, INTERVAL 형식으로 변환하는 방법을 데이터베이스에 알려주기 위해
• 누군가에 의해 변경될지 모르는 기본날짜 형식에 의지하지말자

INSERT INTO T(DATE_COLUMN) VALUES('01/02/03');  -- 기본 날짜 형식이 바뀐다면 ??
INSERT INTO T(DATE_COLUMN) VALUES(TO_DATE('01/02/03','DD/MM/YY'));  -- 03이 1903인지 2003 불확실
INSERT INTO T(DATE_COLUMN) VALUES(TO_DATE('01/02/03','DD/MM/YYYY')); -- 정상

DATE 타입

SQL> CREATE TABLE T (X DATE);

테이블이 생성되었습니다.

SQL>
SQL> INSERT INTO T (X) VALUES (TO_DATE('25-06-2005 12:01:00', 'DD-MM-YYYY HH24:M
I:SS'));

1 개의 행이 만들어졌습니다.

SQL>
SQL> SELECT X, DUMP(X, 10) D FROM T;

X          D
--------  --------------------
05/06/25   Typ=12 Len=7: 120,105,6,25,13,2,1

-- 세기,연도 바이트(DUMP 결과는 120.105)는 100을 추가하여 저장
-- 세기와 연도의 정확한 값을 보려면 100을 빼야한다. 100을 추가하여 표현하는 이유는 B.C(기원전)와 A.D(기원후)를 지원하기위함
-- 세기 바이트에서 100을 뺀 결과가 음수면 B.C를 의미한다

SQL> CREATE TABLE T (X DATE);

테이블이 생성되었습니다.

SQL> ALTER SESSION SET NLS_TERRITORY='AMERICA';

세션이 변경되었습니다.

SQL>
SQL> ALTER SESSION SET NLS_LANGUAGE='AMERICAN';

Session altered.

SQL>
SQL> ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';

Session altered.

SQL>
SQL> INSERT INTO T (X) VALUES (TO_DATE('25-JUN-2005 12:01:00', 'DD-MON-YYYY HH24
:MI:SS'));

1 row created.

SQL> SELECT X, DUMP(X, 10) D FROM T;

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



SQL> INSERT INTO T (X) VALUES (TO_DATE('01-JAN-4712BC', 'DD-MON-YYYYBC HH24:MI:SS'));

1 row created.

SQL> INSERT INTO T (X) VALUES (TO_DATE('01-JAN-4710BC', 'DD-MON-YYYYBC HH24:MI:SS'));

1 row created.

SQL> 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



SQL> CREATE TABLE T(WHAT VARCHAR2(10), X DATE);

Table created.

SQL> INSERT INTO T (WHAT,X) VALUES('ORIG', TO_DATE('25-JUN-2005 12:01:00','DD-MN-YYYY HH24:MI:SS'));

1 row created.

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

4 rows created.

SQL> 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


-- 시분초 필드는 실제 값에서 각가 1을 뺀 1의 자리에 저장
-- 밥 12시는 일자 필드에 1,1,1로 표시
-- 7바이트 포맷은 자연적으로 정렬

DATE 타입에서 시간을 더하거나 빼기

• "DATE 타입에서 어떻게 시간을 더하거나 빼는가?"

• DATE에서 1을 더하면 1을 더한것
• 시,분,초를 더하기 위해서 NUMTODSINTERVAL 내장함수 사용
• 일을 더하기 위해서 간단한 숫자를 더하면됨
• 월이나 연을 더하기위해서 ADD_MONTHS 내장 함수 사용

• NUMTOYMINTERVAL 함수를 사용을 추천하지 않는다. 이유눈 월말에 대한 함수 처리 때문이다.

-- 마지막일자가 31일 이하인 달에 1개월을 더하면 어떤지 확인

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

Session altered.

SQL> SELECT DT, ADD_MONTHS(DT,1)
  2  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

SQL>
SQL> SELECT DT, ADD_MONTHS(DT,1)
  2  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

SQL>
SQL> SELECT DT, ADD_MONTHS(DT,1)
  2  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

SQL>
SQL> SELECT DT, ADD_MONTHS(DT,1)
  2  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

-- 틀린 ...다른결과값을 보여준다
SQL> SELECT DT, DT+NUMTOYMINTERVAL(1,'month')
  2  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

SQL>
SQL> SELECT DT, DT+NUMTOYMINTERVAL(1,'month')
  2  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


-- 에러발생(2001.02.30 은 없음)
SQL> SELECT DT, DT+NUMTOYMINTERVAL(1,'month')
  2  FROM (SELECT TO_DATE('30-JAN-2001','DD-MON-YYYY') DT FROM DUAL);
SELECT DT, DT+NUMTOYMINTERVAL(1,'month')
             *
ERROR at line 1:
ORA-01839: date not valid for month specified



-- 에러발생(2000.02.30 은 없음)
SQL> SELECT DT, DT+NUMTOYMINTERVAL(1,'month')
  2  FROM (SELECT TO_DATE('30-JAN-2000','DD-MON-YYYY') DT FROM DUAL);
SELECT DT, DT+NUMTOYMINTERVAL(1,'month')
             *
ERROR at line 1:
ORA-01839: date not valid for month specified

두 DATE간의 차이 구하기

• 뺄샘 사용, MONTHS_BETWEEN 사용, NUMTODSINTERVAL 사용, NUMTOYMINTERVAL 사용

-- 사용하기 좋은 예는 아니지만 모두 맞는 값이다.

SQL> SELECT DT2-DT1
  2            , MONTHS_BETWEEN(DT2, DT1) MONTHS_BTWN
  3            , NUMTODSINTERVAL (DT2-DT1,'day') DAYS
  4            , NUMTOYMINTERVAL (TRUNC(MONTHS_BETWEEN(DT2,DT1)),'month') MONTHS
  5  FROM ( SELECT TO_DATE('29-FEB-2000 01:02:03', 'DD-MON-YYYY HH24:MI:SS') DT1,
  6                        TO_DATE('15-MAR-2001 11:22:33', 'DD-MON-YYYY HH24:MI:SS') DT2
  7                   FROM DUAL);


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


-- 두 DATE사이의 차이가 1년 15일 10시간 20분 30초라는 걸 확인
SQL> SELECT   NUMTOYMINTERVAL(TRUNC(MONTHS_BETWEEN(DT2, DT1)), 'MONTH') YEARS_MONTHS
  2              , NUMTODSINTERVAL(DT2 - ADD_MONTHS( DT1, TRUNC(MONTHS_BETWEEN(DT2, DT1)) ), 'DAY') DAYS_HOURS
  3   FROM (SELECT TO_DATE('29-FEB-2000 01:02:03', 'DD-MON-YYYY HH24:MI:SS') DT1,
  4                         TO_DATE('15-MAR-2001 11:22:33', 'DD-MON-YYYY HH24:MI:SS') DT2
  5                          FROM DUAL);

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

TIMESTAMP 타입

• TIMESTAMP(N) 형식으로 사용

SQL> DROP TABLE T;

Table dropped.

SQL>
SQL> CREATE TABLE T
  2  (
  3      DT DATE,
  4      TS TIMESTAMP(0)
  5  );

Table created.

SQL>
SQL> INSERT INTO T VALUES(SYSDATE,SYSTIMESTAMP);

1 row created.

-- 데이터 타입은 다르지만 그들이 저장하는 방식은 같다
SQL> SELECT DUMP(DT,10), DUMP(TS,10) FROM T;

DUMP(DT,10)           					 DUMP(TS,10)
---------------------  				     ---------------------------------------
Typ=12 Len=7: 120,114,12,10,21,47,40     Typ=180 Len=7: 120,114,12,10,21,47,41



SQL> DROP TABLE T;

Table dropped.

SQL>
SQL> CREATE TABLE T
  2  (
  3      DT DATE,
  4      TS TIMESTAMP(9)
  5  );

Table created.

-- TIMESTAMP는 11바이트 저장공간 차지, 소수점 자릿수 초단위 표현을 위해 4바이를 사용
SQL> INSERT INTO T VALUES(SYSDATE,SYSTIMESTAMP);

1 row created.

SQL> SELECT DUMP(DT,10), DUMP(TS,10) FROM T;

DUMP(DT,10)  							DUMP(TS,10)
--------------------------------------  ------------------------------------------
Typ=12 Len=7: 120,114,12,10,21,50,44	Typ=180 Len=11: 120,114,12,10,21,50,44,32,17,58,128



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

Session altered.

SQL>
SQL> SELECT * FROM T;

DT                        TS
-----------------------   -------------------------------
10-DEC-2014 20:49:43      10-DEC-14 08.49.43.538000000 PM


SQL>
SQL> SELECT DUMP(TS,16) DUMP FROM T;

DUMP
--------------------------------------------------------------------------------

Typ=180 Len=11: 78,72,c,a,15,32,2c,20,11,3a,80

-- 4바이트를 10진수로 변환하여 확인
SQL> SELECT TO_NUMBER('20113a80','xxxxxxxx') FROM DUAL;

TO_NUMBER('20113A80','XXXXXXXX')
--------------------------------
                       538000000

두 TIMESTAMP 간의 차이를 구하기

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

Session altered.

-- DATE에 적용했던 산술연산 적용가능하다
SQL>
SQL> SELECT SYSTIMESTAMP TS, SYSTIMESTAMP+1 DT FROM DUAL;

TS                                       DT
---------------------------------------- -----------------------------------
12-DEC-14 04.38.48.893000 PM +09:00      13-DEC-2014 16:38:48



-- SYSTIMESTAMP+1 값이 SYSTIMESTAMP 하루 후임을 주목
SQL> SELECT SYSTIMESTAMP TS, SYSTIMESTAMP+NUMTODSINTERVAL(1,'day') DT
  2  FROM DUAL;

TS                                           DT
------------------------------------------  ---------------------------------
12-DEC-14 04.41.24.407000 PM +09:00          13-DEC-14 04.41.24.407000000 PM +09:00


-- DATE와 TIMESTAMP 의 차이점은 DATA에서 DATE를 뺀 숫자
-- TIMESTAMP 에서 TIMESTAMP 를 뺀 값은 INTERVAL

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

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

-- ADD_MONTH 를 사용하면 DT1은 묵시적으로 DATE로 변환, 소수 자릿수 초를 잃음
SQL> SELECT NUMTOYMINTERVAL(TRUNC(MONTHS_BETWEEN(DT2, DT1)), 'MONTH') YEARS_MONTHS
  2                    , DT2 - ADD_MONTHS(DT1, TRUNC(MONTHS_BETWEEN(DT2 ,DT1)))DAYS_HOURS
  3                  FROM (SELECT TO_TIMESTAMP('29-FEB-2000 01:02:03.122000', 'DD-MON-YYYY HH24:MI:SS.FF') DT1,
  4                               TO_TIMESTAMP('15-MAR-2001 11:22:33.000000', 'DD-MON-YYYY HH24:MI:SS.FF') DT2
  5                          FROM DUAL);

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


-- TRUNC(MONTHS_BETWEEN(DT2 ,DT1)) 가 12 이므로 2001.02.29 날짜는 없슴
SQL> SELECT NUMTOYMINTERVAL(TRUNC(MONTHS_BETWEEN(DT2, DT1)), 'MONTH') YEARS_MONTHS
  2                    , DT2 - (DT1 + NUMTOYMINTERVAL(TRUNC(MONTHS_BETWEEN(DT2 ,DT1)), 'MONTH')) DAYS_HOURS
  3                  FROM (SELECT TO_TIMESTAMP('29-FEB-2000 01:02:03.122000', 'DD-MON-YYYY HH24:MI:SS.FF') DT1,
  4                               TO_TIMESTAMP('15-MAR-2001 11:22:33.000000', 'DD-MON-YYYY HH24:MI:SS.FF') DT2
  5                          FROM DUAL);
                  , DT2 - (DT1 + NUMTOYMINTERVAL(TRUNC(MONTHS_BETWEEN(DT2 ,DT1)), 'MONTH')) DAYS_HOURS
                               *
ERROR at line 2:
ORA-01839: date not valid for month specified

TIMESTAMP WITH TIME ZONE 타입

Table dropped.

SQL>
SQL> CREATE TABLE T
  2  (
  3      TS1 TIMESTAMP WITH TIME ZONE,
  4      TS2 TIMESTAMP WITH TIME ZONE
  5  );

Table created.

SQL>
SQL> INSERT INTO T(TS1, TS2)
  2  VALUES ( TIMESTAMP'2010-02-27 16:02:32.212 US/EASTERN',
  3                TIMESTAMP'2010-02-27 16:02:32.212 US/PACIFIC');

1 row created.

SQL>
SQL>
SQL> COMMIT;

Commit complete.

SQL>
SQL> SELECT TS1-TS2 FROM T;

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

-- 두지역은 3시간차이가있다.

TIMESTAMP WITH LOCAL TIMEZONE

SQL> DROP TABLE T;

Table dropped.

SQL> CREATE TABLE T
  2  (
  3      DT DATE,
  4      TS1 TIMESTAMP WITH TIME ZONE,
  5      TS2 TIMESTAMP WITH LOCAL TIME ZONE
  6  );

Table created.

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

1 row created.

SQL>
SQL> SELECT DBTIMEZONE FROM DUAL;

DBTIMEZONE
------------
+00:00

SQL>
SQL> SELECT DUMP(DT), DUMP(TS1), DUMP(TS2) FROM T;

DUMP(DT)  -- 2010-02-27 16:02:32 저장
--------------------------------------------------------------------------------
Typ=12 Len=7: 120,110,2,27,17,3,33

DUMP(TS1)  -- 2010-02-28 00:02:32 (8시간+) 저장
--------------------------------------------------------------------------------
Typ=181 Len=13: 120,110,2,28,1,3,33,12,162,221,0,137,156

DUMP(TS2) -- 2010-02-28 00:02:32 (8시간+) 저장
--------------------------------------------------------------------------------
Typ=231 Len=11: 120,110,2,28,1,3,33,12,162,221,0

SQL> SELECT TS1, TS2 FROM T;


TS1
---------------------------------------------------------------------------
27-FEB-10 04.02.32.212000 PM US/PACIFIC

TS2  -- 9시간+
---------------------------------------------------------------------------
28-FEB-10 09.02.32.212000 AM


SQL> DELETE FROM T;

1 row deleted.

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

1 row created.

SQL>
SQL> SELECT TS1, TS2 FROM T;

TS1
---------------------------------------------------------------------------
12-APR-10 04.02.32.212000 PM US/PACIFIC

TS2
---------------------------------------------------------------------------
13-APR-10 08.02.32.212000 AM

-- TIMESTAMP WITH LOCAL TIME ZONE 컬럼의 데이터를 모두 바꿀수 없다
SQL> 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

INERVAL 타입

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

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

-- EXTRACT 함수를 사용하여 다양한 정보를 구할 수 있다.
SQL> SELECT EXTRACT( DAY    FROM DT2 - DT1) DAY,
  2              EXTRACT( HOUR   FROM DT2 - DT1) HOUR,
  3             EXTRACT( MINUTE FROM DT2 - DT1) MINUTE,
  4             EXTRACT( SECOND FROM DT2 - DT1) SECOND
  5  FROM (SELECT TO_TIMESTAMP('29-FEB-2000 01:02:03.122000', 'DD-MON-YYYY HH24:MI:SS.FF') DT1,
  6                         TO_TIMESTAMP('15-MAR-2001 11:22:33.000000', 'DD-MON-YYYY HH24:MI:SS.FF') DT2
  7        FROM DUAL);

       DAY       HOUR     MINUTE     SECOND
---------- ---------- ---------- ----------
       380         10         20     29.878

• INTERVAL YEAR TO MONTH

-- INTERVAL YEAR(N) TO MONTH

-- 5년2개월의 인터벌
SQL> SELECT NUMTOYMINTERVAL(5, 'YEAR') + NUMTOYMINTERVAL(2, 'MONTH') FROM DUAL;

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

-- 5년2개월 = 62개월
SQL> SELECT NUMTOYMINTERVAL(5 * 12 + 2, 'MONTH') FROM DUAL;

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

-- YEAR/MONTH INTERVAL 타입으로 변환
SQL> SELECT TO_YMINTERVAL('5-2') FROM DUAL;

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

-- NUMBER 필드변환
SQL> SELECT INTERVAL '5-2' YEAR TO MONTH FROM DUAL;

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

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

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

SQL>
SQL> 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

SQL>
SQL> SELECT TO_DSINTERVAL('10 02:03:02.3312') FROM DUAL;

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

SQL>
SQL> SELECT INTERVAL '10 02:03:02.3312' DAY TO SECOND FROM DUAL;

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