Difference between DATE and TIMESTAMP WITH TIME ZONE in Oracle pl/sql
What is Difference between DATE and TIMESTAMP, TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE in Oracle pl/sql?
DATE and TIMESTAMP both allows you to store DAY:MONTH:YEAR HOUR:MINUTE:SECOND
DATE and TIMESTAMP provides 7 bytes for storing all the above mentioned data.
TIMESTAMP was added as an ANSI compliant in Oracle.

But most important, additionally TIMESTAMP allows you to store FRACTIONAL SECONDS.
TIMESTAMP provides 11 bytes for storing FRACTIONAL SECONDS.
TIMESTAMP provides additional 2 bytes for storing TIMEZONE as well.
TIMESTAMP can be used WITH TIME ZONE
and TIMESTAMP can be used WITH LOCAL TIME ZONE
When to use DATE and TIMESTAMP?
If we just need to store time without much precision you must go for DATE.
But, if you need time to be stored with lot of precision where even fractional seconds could matter.

Example - In casinos, bidding and jackpot games where even fractional seconds could matter.
In stock markets for maintaining real time data of highly traded stocks.



How to use/insert/select DATE in Oracle pl/sql?
Create table to store DATE in Oracle pl/sql>
create table TEST_DATE( CREATION_DATE DATE);


How to insert data\time into table in Oracle (using Oracle reserved keyword sysdate)>
insert into TEST_DATE ( CREATION_DATE) values( sysdate);
Now, execute - delete from TEST_DATE
Now, insert user date\time into table using TO_DATE function in Oracle>
insert into TEST_DATE (CREATION_DATE) values (TO_DATE('2015/12/22 13:15:19', 'YYYY/MM/DD HH:MI:SS'));
select data from table in Oracle pl/sql>
select * from TEST_DATE;
select DATE from table using TO_CHAR function in Oracle pl/sql>
SELECT TO_CHAR(CREATION_DATE,'MM/DD/YYYY HH24:MI:SS')CREATION_DATE from TEST_DATE;
How to convert DATE to TIMESTAMP in Oracle pl/sql?
SELECT CAST(CREATION_DATE AS TIMESTAMP) CREATION_DATE  FROM TEST_DATE;




select DATE from dual in Oracle pl/sql>
select sysdate from dual;


select user DATE from dual in Oracle pl/sql>
select TO_DATE('2015/12/22 11:15:19', 'YYYY/MM/DD HH24:MI:SS') DATE_ from dual;


select DATE from dual using TO_CHAR function in Oracle pl/sql>
select TO_CHAR(TO_DATE('2015/12/22 11:15:19', 'YYYY/MM/DD HH24:MI:SS'),'MM/DD/YYYY HH24:MI:SS')DATE_ from dual;






How to use/insert/select TIMESTAMP in Oracle pl/sql?
Create table to store TIMESTAMP in Oracle pl/sql>
create table TEST_TIMESTAMP(CREATION_TIMESTAMP TIMESTAMP);


How to insert TIMESTAMP into table in Oracle (using Oracle reserved keyword CURRENT_TIMESTAMP)>
insert into TEST_TIMESTAMP (CREATION_TIMESTAMP) values(CURRENT_TIMESTAMP);
How to insert TIMESTAMP into table in Oracle pl/sql (using Oracle reserved keyword SYSTIMESTAMP)>
insert into TEST_TIMESTAMP (CREATION_TIMESTAMP) values (SYSTIMESTAMP );


Now, execute - delete from TEST_TIMESTAMP
Now, insert user TIMESTAMP into table using TO_TIMESTAMP function in Oracle>
insert into TEST_TIMESTAMP (CREATION_TIMESTAMP) values (TO_TIMESTAMP('2015/12/22 11:15:19.341', 'YYYY/MM/DD HH:MI:SS.FF'));
Note : FF is used to store fractional seconds.


select data from table in Oracle pl/sql>
select * from TEST_TIMESTAMP;


select TIMESTAMP from table using TO_CHAR function in Oracle pl/sql>
SELECT TO_CHAR(CREATION_TIMESTAMP,'MM/DD/YYYY HH24:MI:SS.FF') CREATION_DATE from TEST_TIMESTAMP;
SELECT TO_CHAR(CREATION_TIMESTAMP,'MM/DD/YYYY HH24:MI:SS.FF3') CREATION_DATE  FROM TEST_TIMESTAMP;
SELECT TO_CHAR(CREATION_TIMESTAMP,'MM/DD/YYYY HH24:MI:SS.FF6') CREATION_DATE  FROM TEST_TIMESTAMP;
SELECT TO_CHAR(CREATION_TIMESTAMP,'MM/DD/YYYY HH24:MI:SS.FF9') CREATION_DATE  FROM TEST_TIMESTAMP;
FF appended with numeric digit is the number of digits that will be displayed in fractional seconds.





select TIMESTAMP from dual in Oracle pl/sql>
select localtimestamp from dual;



select user TIMESTAMP from dual in Oracle>
select TO_TIMESTAMP('2015/12/22 11:15:19.341', 'YYYY/MM/DD HH:MI:SS.FF') TIMESTAMP_ from dual;







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 pl/sql >
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 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 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 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;





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.
How to use/insert/select TIMESTAMP WITH LOCAL TIME ZONE in Oracle pl/sql?
Create table to store TIMESTAMP WITH LOCAL TIME ZONE in Oracle>
create table TEST_TIMESTAMP_TIMEZONE1 ( LOCAL_TIME_ZONE TIMESTAMP WITH LOCAL TIME ZONE, TIME_ZONE_ TIMESTAMP WITH TIME ZONE);


How to insert 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?
Use either of the following
select CURRENT_TIMESTAMP from dual;
select SYSTIMESTAMP from dual;


Or, simple way is to use

SELECT SESSIONTIMEZONE FROM dual;






Convert TIMESTAMP with Timezone to DATE in Oracle
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
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
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;


Labels: Oracle
eEdit
Must read for you :