Difference between Oracle DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE

In Oracle there are four date&time datatype:

1. DATE
2. TIMESTAMP
3. TIMESTAMP WITH TIME ZONE
4. TIMESTAMP WITH LOCAL TIME ZONE

What is difference between this datatypes???

       TIMESTAMP datatype differs from DATE only with that it includes fractional seconds. But TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE is more interesting :-)

       Note that the DATE and TIMESTAMP datatypes do not support time zone differences. However, there are two datatypes that do work with time zones. They are called TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE. The TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE datatypes differ in the way they handle time zones. Both datatypes store the source data’s date and time. The former TIMESTAMP WITH TIME ZONE—also stores the time zone. The latter—TIMESTAMP WITH LOCAL TIME ZONE does not store the time zone but stores the time normalized to UTC time. When queried, it presents its data in the user’s local time zone. It uses an offset from UTC time.

Let's look at an example:
First open cmd and open sqlplus. for example: >sqlplus hr/hr

Show wich timezone set in our database server. For example in my case it's +05:00

select sessiontimezone from dual;

Then create table t1 with 2 columns as following

create table t1(tswithlocaltz timestamp with local time zone, tswithtz timestamp with time zone);

Now add two rows into table without timezone and with timezone

insert into t1 values(to_timestamp('05-06-2012 16:40:13', 'DD-MM-YYYY HH24:MI:SS'), to_timestamp('05-06-2012 16:40:13', 'DD-MM-YYYY HH24:MI:SS'));

insert into t1 values(to_timestamp_tz('05-06-2012 16:40:13 +04:00', 'DD-MM-YYYY HH24:MI:SS TZH:TZM'), to_timestamp_tz('05-06-2012 16:40:13 +04:00', 'DD-MM-YYYY HH24:MI:SS TZH:TZM'));

commit;

Now u can select from this table to see your result:

select to_char(tswithlocaltz, 'DD-MM-YYYY HH24:MI:SS') || '    ' AS "with local", to_char(tswithtz, 'DD-MM-YYYY HH24:MI:SS TZH:TZM') "without local" from t1;

What'll be results for both two columns and rows?
A?
A?
A? :)

look at first column and second row. The hour is 17. Why? Because u inserted datetime with time zone UTC+4 but in db it is local time zone +5 so it added 1hour then show it us as 17 :) That is why u can't change database time zone if there is at least one table with the "timestamp with local time zone" datatype.

That is all I wanted to say ...