Datetime and Interval Datatypes Description in Oracle

DateTime DataTypes
--------------------------------------
1)DATE Datatype
----------------------------------
•To store date and time in a table you can use DATE datatype in oracle.
•To insert DATE datatype in a table you have to use either date value as a literal or convert by TO_DATE funcation.
An example,

SQL> create table a_t (a date);
Table created.
As a literal,
SQL> insert into a_t values ( DATE '11-02-07');
1 row created.
Using TO_DATE function,
SQL> insert into a_t values (to_date('10-02-07','DD-MM-yy'));
1 row created.


SQL> select * from a_t;
A
---------
07-FEB-11
10-FEB-07

2)TIMESTAMP Datatype
---------------------------------------
•It stores the year, month, and day of the DATE datatype, plus hour, minute, and second values.
The fields are discussed in http://arjudba.blogspot.com/2008/06/datetime-and-interval-datatypes-in.html
•It is an extension of DATE datatype.
•To convert character data to timestamp values use TO_TIMESTAMP function.
3)TIMESTAMP WITH TIME ZONE Datatype
------------------------------------------------
•TIMESTAMP WITH TIME ZONE is a variant of TIMESTAMP that includes a time zone offset in its value.
•This datatype is really useful for collecting and evaluating date information across geographic regions.

4)TIMESTAMP WITH LOCAL TIME ZONE Datatype
----------------------------------------------------------
•TIMESTAMP WITH LOCAL TIME ZONE is another variant of TIMESTAMP that includes a time zone offset in its value.
•This datatype differs from TIMESTAMP WITH TIME ZONE in that data stored in the database is normalized to the database time zone, and the time zone offset is not stored as part of the column data. When a user retrieves the data, Oracle returns it in the user's local session time zone.

Interval DataTypes
-----------------------------------------
1)INTERVAL YEAR TO MONTH Datatype
---------------------------------------------
•This datatype stores a period of time using the YEAR and MONTH datetime fields.

•When we want to store the difference between two datetime values in terms of year and months then we can use this datatype.

2)INTERVAL DAY TO SECOND Datatype
---------------------------------------------------
•This datatype stores a period of time in terms of days, hours, minutes, and seconds.

•This datatype is useful for representing the actual difference between two datetime values.

Examples:
------------------------
SQL>CREATE TABLE with_date_interval (date_dt DATE, timest_dt TIMESTAMP, timest_wtz TIMESTAMP WITH TIME ZONE, timest_wltz TIMESTAMP WITH LOCAL TIME ZONE,
int_1 INTERVAL YEAR TO MONTH, int_2 INTERVAL DAY TO SECOND);
Table created.

SQL> desc with_date_interval;
Name Null? Type
----------------------------------------- -------- ----------------------------
DATE_DT DATE
TIMEST_DT TIMESTAMP(6)
TIMEST_WTZ TIMESTAMP(6) WITH TIME ZONE
TIMEST_WLTZ TIMESTAMP(6) WITH LOCAL TIME
ZONE
INT_1 INTERVAL YEAR(2) TO MONTH
INT_2 INTERVAL DAY(2) TO SECOND(6)

SQL>insert into with_date_interval values(DATE '11-01-08', SYSTIMESTAMP, SYSTIMESTAMP, SYSDATE,INTERVAL '10-2' YEAR(3) TO MONTH, INTERVAL '7 8:10:10.100' DAY TO SECOND(3)) ;
1 row created.

SQL> select * from with_date_interval;

DATE_DT TIMEST_DT TIMEST_WTZ TIMEST_WLTZ INT_1 INT_2
-------------------- --------------------
08-JAN-11 01-JUL-08 02.19.20.238715 AM 01-JUL-08 02.19.20.238715 AM -04:00 01-JUL-08 02.19.20.000000 AM +10-02 +07 08:10:10.100000

Related Documents:
------------------------
Datetime and Interval Datatypes Fields and Values in Oracle