14일차 # 5-10: Timestamp Data Type

- consists of datetime fields: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND

                                 TimeZone_Hour, TimeZone_Minute, TimeZone_Region, TimeZone_Abbr

  

  1) TIMESTAMP ( TIMEZONE 저장 X         )

  2) TIMESTAMP WITH TIME ZONE ( TIMEZONE 저장 O, 출력 O )

  3) TIMESTAMP WITH LOCAL TIME ZONE ( TIMEZONE 저장 O, 출력 X )

- stored   : normalized to the db time zone

- retrieved: adjusted to the session time zone

alter session set time_zone = '+09:00';

drop table ts_test purge;

create table ts_test (

 ts timestamp(0)

, ts_wtz timestamp(4) with time zone

, ts_wltz timestamp with local time zone --> default: 6자리

);

insert into ts_test values (current_date, current_date, current_date);

insert into ts_test values (sysdate, sysdate, sysdate);

commit;

col ts format a30;

col ts_wtz format a35;

col ts_wltz format a30;

select * from ts_test;

alter table ts_test

 modify (ts timestamp(9), ts_wtz timestamp(3) with time zone);

==> ERROR : ORA-30082: datetime/interval column to be modified must be empty to decrease fractional second or leading field precision

alter table ts_test

 modify (ts timestamp(9), ts_wtz timestamp(7) with time zone);

select * from ts_test;