Difference between Date, Timestamp, Timestamp with Time Zone and Timestamp with Local Time Zone

For more info, visitLet's  create a table to understand the difference between these four. The table script will be create table mytime(datetime1 DATE,datetime2 TIMESTAMP,datetime3 TIMESTAMP WITH TIME ZONE,datetime4 TIMESTAMP WITH LOCAL TIME ZONE); We have created a table with four columns with datatypes of DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE. Now, let's understand what their definitions have to say about them. DATE: Can store a date and the time resolved to seconds. TIMESTAMP: DATE and TIMESTAMP are almost same, with the only difference that TIMESTAMP is able to resolve to a billionth of a second, i.e. it has 9 decimal places of precision for a second. TIMESTAMP WITH TIME ZONE: Contains all the features of a TIMESTAMP and additionally, stores the TIME ZONE information with it. TIMESTAMP WITH LOCAL TIME ZONE: This is one of the tricky datatype. Though I am writing its definition, but I am sure it won't be much clear until we see an example. The definition is Its name has TIME ZONE in it, it doesn't actually stores the time zone. Whenever we store a date time with time zone information in this field, it converts the date time with time zone information into database time zone information, and stores it. After this, whenever any session fetches the information from this field, it first converts the information into the local session time zone and presents it. We have already created a table mytime. Now let's insert values in it. Our insert script will be insert into mytime values(to_date('20-may-2012 10:30:40','dd-Mon-yyyy hh:mi:ss'),TO_TIMESTAMP('20-may-2012 10:30:40.123456 AM','dd-Mon-yyyy hh:mi:ss.FF6 AM'),TO_TIMESTAMP_TZ('20-may-2012 10:30:40.123456 AM EST','dd-Mon-yyyy hh:mi:ss.FF6 AM TZR'),TO_TIMESTAMP_TZ('20-may-2012 10:30:40.123456 AM EST','dd-Mon-yyyy hh:mi:ss.FF6 AM TZR')); We are inserting values in four columns as shown above. Now, alter your session and set your time zone to -7.00 as shown below. ALTER session SET time_zone='-7:00'; Why did we change the session time zone? We will get to know in more detail after seeing the result of the following select query. select * from mytime; And the output is DATETIME1 -------------------- DATETIME2 --------------------------------------------------------------------------- DATETIME3 --------------------------------------------------------------------------- DATETIME4 --------------------------------------------------------------------------- 20-may-2012 10:30:40 20-MAY-12 10.30.40.123456 AM 20-MAY-12 10.30.40.123456 AM EST 20-MAY-12 07.30.40.123456 AM DATETIME1 reports the date and time in simple format. DATETIME2 reports the date and time with extra precision in seconds. DATETIME3 reports the date and time with time zone. DATETIME4 is the most interesting. It calculates and reports the date and time according to the local session timezone. NOTE: By default DATETIME1 column will not display the time information. You will have to alter your session as follows. Alter session set NLS_DATE_FORMAT='dd-mon-yyyy hh:mi:ss';