Tuesday, June 5, 2012

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 ...

6 comments:

  1. Replies
    1. Aslamu Alaikum Vasif. I am preparing for SQL expert exam 1z0-047 .
      I am following Steve Hearn `s book .Is it enough to pass the exam.
      And the book have lot of topics and it is hard to remember all at once .
      Can you share your preparation path.

      Delete
    2. Salam bro, the best way to succed the 1z0-047 exam, is to pass the maximum ammout of vce exams ( visual certif exam), google for vce files or pdf exam

      Delete
  2. • Nice and good article. It is very useful for me to learn and understand easily. Thanks for sharing your valuable information and time. Please keep updating. Power Bi Online course hyderabad

    ReplyDelete