In this oracle tutorial we will learn how to use/insert/select TIMESTAMP WITH TIME ZONE in Oracle pl/sql.
How to use/insert/select TIMESTAMP WITH TIME ZONE in Oracle pl/sql?
Create table to store TIMESTAMP WITH TIME ZONE in Oracle pl/sql>
create table TEST_TIMESTAMP_TIMEZONE( CREATION_TIMESTAMP_TIMEZONE TIMESTAMP WITH TIME ZONE);
How to insert TIMESTAMP WITH TIME ZONE into table in Oracle pl/sql (using Oracle reserved keyword CURRENT_TIMESTAMP)>
insert into TEST_TIMESTAMP_TIMEZONE (CREATION_TIMESTAMP_TIMEZONE) values (CURRENT_TIMESTAMP);
select * from TEST_TIMESTAMP_TIMEZONE;
Now, execute - delete from TEST_TIMESTAMP_TIMEZONE
Now, insert user TIMESTAMP WITH TIME ZONE into table using TO_TIMESTAMP_TZ function in Oracle >
insert into TEST_TIMESTAMP_TIMEZONE (CREATION_TIMESTAMP_TIMEZONE) values (TO_TIMESTAMP_TZ('2015/12/22 11:15:19.341 +05:30', 'YYYY/MM/DD HH:MI:SS.FF TZH:TZM'));
select * from TEST_TIMESTAMP_TIMEZONE;
Now, execute - delete from TEST_TIMESTAMP_TIMEZONE
Now, insert user TIMESTAMP WITH TIME ZONE into table using TO_TIMESTAMP_TZ function in Oracle pl/sql >
insert into TEST_TIMESTAMP_TIMEZONE (CREATION_TIMESTAMP_TIMEZONE) values (TO_TIMESTAMP_TZ('2015/12/22 11:15:19.341 ASIA/CALCUTTA, 'YYYY/MM/DD HH:MI:SS.FF TZR'));
Note : FF is used to store fractional seconds.
select TIMESTAMP WITH TIME ZONE from dual in Oracle pl/sql>
select CURRENT_TIMESTAMP from dual;
select SYSTIMESTAMP from dual;
select user TIMESTAMP from dual in Oracle pl/sql using TO_TIMESTAMP_TZ function and TZR format>
select TO_TIMESTAMP_TZ('2015/12/22 11:15:19.341 GMT', 'YYYY/MM/DD HH:MI:SS.FF TZR') CREATION_TIMESTAMP_TIMEZONE from dual;
select user TIMESTAMP from dual in Oracle pl/sql using TO_TIMESTAMP_TZ function and TZR format>
select TO_TIMESTAMP_TZ('2015/12/22 11:15:19.341 ASIA/CALCUTTA', 'YYYY/MM/DD HH:MI:SS.FF TZR') CREATION_TIMESTAMP_TIMEZONE from dual;
select user TIMESTAMP from dual in Oracle pl/sql using TO_TIMESTAMP_TZ function and TZM format>
select TO_TIMESTAMP_TZ('2015/12/22 11:15:19.341 +05:30', 'YYYY/MM/DD HH:MI:SS.FF TZH:TZM') CREATION_TIMESTAMP_TIMEZONE from dual;
In this oracle tutorial we learned how to use/insert/select TIMESTAMP WITH TIME ZONE in Oracle pl/sql.
Labels:
Oracle