Difference between TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE in Oracle pl/sql


In this oracle tutorial we will learn what are differences between TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE in Oracle pl/sql.



What is TIMESTAMP WITH LOCAL TIME ZONE ?
TIMESTAMP WITH LOCAL TIME ZONE - does not store TIME ZONE in database. When TIMESTAMP WITH LOCAL TIME ZONE is stored in database it converts it into database time zone.
When it is fetched from database Oracle converts it into the local session time zone.

But, TIMESTAMP WITH TIME ZONE doesn’t have anything to do with local session time zone it stores the TIMESTAMP WITH TIME ZONE of current database and retrieves the TIMESTAMP WITH TIME ZONE stored in database.


Let’s discuss both of them in detail with examples.



Create table to store TIMESTAMP WITH LOCAL TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE in Oracle pl/sql>
create table TEST_TIMESTAMP_TIMEZONE1 ( LOCAL_TIME_ZONE TIMESTAMP WITH LOCAL TIME ZONE, TIME_ZONE_ TIMESTAMP WITH TIME ZONE);


insert TIMESTAMP WITH LOCAL TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE into table in Oracle (using Oracle reserved keyword CURRENT_TIMESTAMP)>
insert into TEST_TIMESTAMP_TIMEZONE1 (LOCAL_TIME_ZONE, TIME_ZONE_) values (CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);


select * from TEST_TIMESTAMP_TIMEZONE1;

LOCAL_TIME_ZONE = 07-DEC-15 04.21.55.019000000 PM
TIME_ZONE_             = 07-DEC-15 04.21.55.019000000 PM ASIA/CALCUTTA


My current TIME ZONE is ASIA/CALCUTTA  i.e. UTC+05:30
Scroll down the post to find out your current TIME ZONE.


Now, change the database SESSIONTIMEZONE using alter session command(It will temporarily change your database TIME ZONE/local session time zone  i.e. for current session only)
ALTER session SET TIME_ZONE='+06:30';


Again execute select query. Now,select result will change
select * from TEST_TIMESTAMP_TIMEZONE1;

LOCAL_TIME_ZONE = 07-DEC-15 05.21.55.019000000 PM
TIME_ZONE_             = 07-DEC-15 04.21.55.019000000 PM ASIA/CALCUTTA


So, what happened above?
TIMESTAMP WITH LOCAL TIME ZONE didn’t store TIME ZONE in database. When TIMESTAMP WITH LOCAL TIME ZONE was stored in database it converted it into database time zone.
When TIMESTAMP WITH LOCAL TIME ZONE was fetched from database Oracle converted it into the local session time zone.

But, TIMESTAMP WITH TIME ZONE doesn’t have anything to do with local session time zone it stores the TIMESTAMP WITH TIME ZONE of current database and retrieves the TIMESTAMP WITH TIME ZONE stored in database.

Advantage of using TIMESTAMP WITH LOCAL TIME ZONE ?
It helps in solving time zone related issues across databases existing in multiple locations.


How to find my current database TIME ZONE in oracle pl/sql?
Use either of the following -
select CURRENT_TIMESTAMP from dual;

select SYSTIMESTAMP from dual;


Or, simple way is to use

SELECT SESSIONTIMEZONE FROM dual;


So in this oracle tutorial we learned what are differences between TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE in Oracle pl/sql.

Labels: Oracle
eEdit
Must read for you :