Convert TIMESTAMP with Timezone to DATE in Oracle


In this oracle tutorial we will learn how Convert TIMESTAMP with Timezone to DATE  in Oracle pl/sql.

Convert TIMESTAMP with Timezone to DATE in Oracle pl/sql



Firstly let’s Create table to store TIMESTAMP WITH TIME ZONE in Oracle pl/sql>
create table TEST_TIMESTAMP_TIMEZONE( CREATION_TIMESTAMP_TIMEZONE TIMESTAMP WITH TIME ZONE);


Now, let’s insert user TIMESTAMP WITH TIME ZONE into table in Oracle  pl/sql>

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 10:30:00.000 +05:30', 'YYYY/MM/DD HH:MI:SS.FF TZH:TZM'));

select * from TEST_TIMESTAMP_TIMEZONE;

Or,

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 10:30:00.000 ASIA/CALCUTTA, 'YYYY/MM/DD HH:MI:SS.FF TZR'));


Note : FF is used to store fractional seconds.


Convert TIMESTAMP with Timezone (ASIA/CALCUTTA UTC+05:30) to DATE in (GMT UTC+00:00)   in Oracle pl/sql >
SELECT TO_CHAR (FROM_TZ (CAST (CREATION_TIMESTAMP_TIMEZONE AS TIMESTAMP), 'ASIA/CALCUTTA') AT TIME ZONE 'GMT', 'DD-MON-YYYY HH24:MI:SS') GMT
FROM TEST_TIMESTAMP_TIMEZONE;

Or,
SELECT TO_CHAR (FROM_TZ (CAST (CREATION_TIMESTAMP_TIMEZONE AS TIMESTAMP), 'ASIA/CALCUTTA') AT TIME ZONE '+00:00', 'DD-MON-YYYY HH24:MI:SS') GMT
FROM TEST_TIMESTAMP_TIMEZONE;



Convert TIMESTAMP with Timezone (ASIA/CALCUTTA UTC+05:30) to DATE in (Australia/Sydney UTC+11:00)   in Oracle pl/sql>
SELECT TO_CHAR (FROM_TZ (CAST (CREATION_TIMESTAMP_TIMEZONE AS TIMESTAMP), 'ASIA/CALCUTTA') AT TIME ZONE 'Australia/Sydney', 'DD-MON-YYYY HH24:MI:SS') Australia_Sydney
FROM TEST_TIMESTAMP_TIMEZONE;

Or,
SELECT TO_CHAR (FROM_TZ (CAST (CREATION_TIMESTAMP_TIMEZONE AS TIMESTAMP), 'ASIA/CALCUTTA') AT TIME ZONE '+11:00', 'DD-MON-YYYY HH24:MI:SS') Australia_Sydney
FROM TEST_TIMESTAMP_TIMEZONE;



So in this oracle tutorial we learned how Convert TIMESTAMP with Timezone to DATE  in Oracle pl/sql.

Labels: Oracle
eEdit
Must read for you :