TIMESTAMP WITH TIME ZONE in Oracle pl/sql


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.











No comments:

Post a Comment