[SQL] 날짜 데이터 타입 - date, timestamp

 

오라클에서 제공하는 날짜 데이터 타입 중

자주 사용하는 타입은 DATE와 TIMESTAMP이다.

 

보통 날짜 데이터의 경우 사용자가 직접 입력하기 보단

특정 시점에 데이터를 저장할 때 그 시점을 자동으로

저장하는 용도로 많이 사용한다. 

그래서 INSERT문으로 데이터를 입력할 때

날짜와 관련된 값은 디폴트값으로 저장하는 경우가 많다.

 

얼마나 세밀한 데이터가 필요한 지에 따라,

상황에 따라 DATE나 TIMESTAMP를 선택할 수 있다.

사실 이 두 타입 말고도 날짜형 데이터 타입이 더 있긴 하다.

예를 들면, INTERVAL_DAY TO SECOND,

TIMESTAMP WITH TIME ZONE,

TIMESTAMP WITH LOCAL TIME ZONE 등이 있다.

 

DATE

 

DATE 는 연,월,일,시,분,초까지 입력 가능하다.

저장가능한 시점은 무려...

BC 4712년 1월 1일부터

9999년 12월 31일까지이다.

 

TIMESTAMP

 

TIMESTAMP는 DATE와 연,월,일,시,분,초까지

저장할 수 있다는 점에서는 유사하지만

차이가 있다면, 초 단위를 좀 더 세부적으로 입력할 수 있는데

밀리초까지 입력가능하다.

 

fractional_seconds_precision은

TIMESTAMP 값의 소수 초 부분의 정밀도를 나타낸다.

0~9까지 정수를 입력할 수 있고 디폴트 값은 6이다.

 

예를 들어 테이블 생성문을 작성할 때

해당 컬럼의 데이터 타입을 TIMESTAMP(3)으로

설정하면 소수 초의 세 자리까지 저장가능하다.

YY/MM/DD HH:MM:SS. 다음에

0부터 999까지의 값을 나타낼 수 있다. 

 

TIMESTAMP를 선택하면 SELECT 시

원하는 만큼의 초단위를 설정할 수 있으니

초단위까지 중요한 데이터라면 

TIMESTAMP를 사용하는 것이 

더 적절한 선택일 수 있다.

 

 

아래 예시를 통해 간단히 살펴보자. 

 

예시 1 : DATE와 TIMESTAMP의 DEFAULT 값 /시스템 기준값 삽입

 

날짜형 데이터 타입의 값에

DEFAULT값이 삽입되도록 초기에 설정하려면

테이블 생성 시 해당 컬럼의 DEFAULT값을

SYSDATE나 SYSTIMESTAMP로 정의해두면 된다.

//예시 테이블 example3 생성
CREATE TABLE example3 (
col_date DATE DEFAULT SYSDATE,
col_timestamp TIMESTAMP DEFAULT SYSTIMESTAMP
);
// DEFAULT값으로 데이터 삽입
INSERT INTO example3 VALUES (DEFAULT, DEFAULT);
// 데이터 조회
SELECT * FROM example3;

 

 

 

 

그 외에도 별도로 DEFAULT값을 지정하지 않아도

INSERT문에서 시스템 기준으로 날짜 데이터를 삽입하려면

아래와 같이 INSERT 문을 작성하면 된다.

//예시 테이블 example3 생성
CREATE TABLE example3 (
col_date DATE,
col_timestamp TIMESTAMP
);
// T시스템 기준값으로 데이터 삽입
INSERT INTO example3 VALUES (SYSDATE, SYSTIMESTAMP);
// 데이터 조회
SELECT * FROM example3;

 

실제로 실행해보고 반환되는 데이터를 자세히 살펴보겠다.

위에서 DATE 타입으로는 연,월,일,시,분,초까지 저장 가능하다고

했는데 실제로 출력해보니 연,월,일까지만 반환된 것을 볼 수 있다.

 

TIMESTAMP 타입의 경우 초단위가 더 세밀한 값으로 조회되는 것을

확인할 수 있다. 19:04:27 뒤에 .으로 구분한 다음 오는

9자리의 숫자는 위에서 말한 fractional_seconds_precision을 의미한다.

 

 

 

예시 2 : DATE 타입에서 시,분,초까지 조회하기

 

예시1과 같이 DATE타입으로 저장된 값을

그대로 조회하면 연,월,일 - YY/MM/DD까지만 출력되지만,

TO_CHAR를 사용해서 출력될 형식을 지정하면

시,분,초까지도 조회가 가능하다. 

 

위에서 언급한대로 TIMESTAMP형으로 저장한 경우

세밀한 초단위까지 조회가 가능한데, 이 단위도 

속성을 어떻게 정의하느냐에 따라 조절 가능하다.

 

예) 테이블 생성문에서 해당 컬럼 속성 지정 시

col_timestamp TIMESTAMP(*), 와 같이 작성하면 된다.

*자리에 0~9까지 올 수있음

 

속성을 변경하지 않더라도 SELECT문에서 별도로

원하는 데이터 출력 형식을 지정하면 

원하는 단위까지 조회를 할 수 있다. 

물론 처음에 정밀도를 몇 단위까지 저장가능하게

했는지에 따라 조회 가능한 단위가 제한된다는 점에

유의해야 한다. 

 

예시 3 :  DATE와 TIMESTAMP에 직접 값 삽입

 

예시 1과 같이 DEFAULT값이 아니라

특정 값을 직접 입력할 때는 

아래와 같이 INSERT문을 작성하면 된다.

//example3 테이블에 특정 값을 INSERT하는 쿼리
INSERT INTO example3 (col_date, col_timestamp) VALUES (
    '2024-02-18', 
    TIMESTAMP '2024-02-18 12:34:56.789'
);

 

 TIMESTAMP 타입의 경우는 위의 예시와 같이 

TIMESTAMP라고 리터럴을 명시하는 것이 

정확한 데이터를 저장하기 위해 필요하다.

 

 

 

예시 4 : TIMESTAMP 타입의 세부 초단위 조절하기

 

여기서는 SELECT문을 어떻게 작성하느냐에 따라

초단위를 조절할 수 있는지 예시를 통해 살펴보자.

출력되는 열의 길이가 너무 길면 보기 불편해서

UNION ALL을 활용해서 세부 초단위 변화에 대한

출력값을 세로로 볼 수 있도록 출력해보았다. 

 

(집계하여 반환하는 경우에는 컬럼을

세로로 출력하려면 PIVOT을 활용하는데

여기서는 세부 단위를 집계하는 것이 아니고

값 그대로를 출력하는 상황이라 UNION ALL을 활용했다.)

SELECT
    TO_CHAR(col_timestamp, 'YYYY-MM-DD HH24:MI:SS') AS "Date_Time" FROM example3
UNION ALL    
SELECT
    TO_CHAR(col_timestamp, 'YYYY-MM-DD HH24:MI:SS.FF1') FROM example3
UNION ALL
SELECT
    TO_CHAR(col_timestamp, 'YYYY-MM-DD HH24:MI:SS.FF2') FROM example3
UNION ALL
SELECT
    TO_CHAR(col_timestamp, 'YYYY-MM-DD HH24:MI:SS.FF3') FROM example3
UNION ALL
SELECT
    TO_CHAR(col_timestamp, 'YYYY-MM-DD HH24:MI:SS.FF4') FROM example3
UNION ALL
SELECT
    TO_CHAR(col_timestamp, 'YYYY-MM-DD HH24:MI:SS.FF5') FROM example3
UNION ALL
SELECT
    TO_CHAR(col_timestamp, 'YYYY-MM-DD HH24:MI:SS.FF6') FROM example3
UNION ALL
SELECT
    TO_CHAR(col_timestamp, 'YYYY-MM-DD HH24:MI:SS.FF7') FROM example3
UNION ALL
SELECT
    TO_CHAR(col_timestamp, 'YYYY-MM-DD HH24:MI:SS.FF8') FROM example3
UNION ALL
SELECT
    TO_CHAR(col_timestamp, 'YYYY-MM-DD HH24:MI:SS.FF9') FROM example3;

 

반환된 값을 보면,

 

각 SELECT문에서는 각각의 세부 초 단위에 따라

다른 정밀도를 가진 데이터를 출력하도록 설정했다.

각각의 'FF' 포맷은 소수 초를 나타내고,

여기에 따라 세부 초의 정밀도를 조절하여 점차

세부적인 초단위가 반환되도록 했다. 

 

세부 초단위에 대한 설명은 아래 표를 참고하면 된다.

+--------------------------+-----------------------+
| Fractional Seconds (FF)  |          설명         |
+--------------------------+-----------------------+
| FF0 (Seconds)            | 초                    |
| FF1 (Tenths)             | 0.1 초                |
| FF2 (Hundredths)         | 0.01 초               |
| FF3 (Milliseconds)       | 0.001 초              |
| FF4 (Ten-Millionths)     | 0.0001 초             |
| FF5 (Hundred-Millionths) | 0.00001 초            |
| FF6 (Microseconds)       | 0.000001 초           |
| FF7 (Hundred-Nanoseconds)| 0.0000001 초          |
| FF8 (Ten-Nanoseconds)    | 0.00000001 초         |
| FF9 (Nanoseconds)        | 0.000000001 초        |
+--------------------------+-----------------------+