DATE / TIMESTAMP DATATYPES.

We have two options to store the date in oracle table.
1. DATE DATATYPE
2. TIMESTAMP DATATYPE

1. DATE DATATYPE:
It has the ability to store the month, day, year, century, hours, minutes, and seconds.
The problem with the DATE datatype is it's granularity when trying to determine a time interval
between two events when the events happen within a second of each other.

2. TIMESTAMP DATATYPE:
Oracle has expanded on the DATE datatype and has given us the TIMESTAMP datatype which stores all the information that the DATE datatype stores, but also includes fractional seconds.

Trunc can't be used on TIMESTAMP DATATYPE columns.

Example:

select TO_CHAR(time1,'MM/DD/YYYY HH24:MI:SS:FF3') from table;

Where FF, represents the Fractional seconds part.

Calculating the time difference between two TIMESTAMP datatypes is much easier than the DATE datatype.

When we just do straight subtraction of the columns,we see, the results are much easier to recognize,  X days, Y hours, Z minutes, and F seconds.


In DATE we use : SYSDATE
IN TimeStamp we use: SYSTIMESTAMP

Example:

SELECT SYSTIMESTAMP FROM DUAL;


Oracle's TIME ZONE feature of the TIMESTAMP datatype:

1. WITH TIME ZONE
2. WITH LOCAL TIME ZONE

We have two options when setting the timezone of a database.

1. By specifying the displacement (hh:mi) from GMT/UTC.
2. By specifying the name in V$TIMEZONE.

The timezone of the database is set at the time of creation & it can be altered.

select DBTIMEZONE from dual;

ALTER database SET TIME_ZONE = '-04:00';

We need to bounce back the database to make the alter affective.

We can set at session level

Alter session set TIME_ZONE='-03:00';

Select SESSIONTIMEZONE from dual;


SYSTEMTIMESTAMP : This will return the sysdate with time zone information.

SELECT SYSTIMESTAMP FROM DUAL;

10/4/2010 4:13:56.200918 PM -04:00

If we have set the time zone info at the session level, we need to use CURRENT_TIMESTAMP

select CURRENT_TIMESTAMP from dual;

Example:

Create table date_table (
                                      time_stamp_tz   TIMESTAMP WITH TIME ZONE,
                                      time_stamp_ltz  TIMESTAMP WITH LOCAL TIME ZONE
                                     );


Select dbtimezone,sessiontimezone from dual;

Insert into date_table values (SYSTIMESTAMP,SYSTIMESTAMP);

Alter session set time_zone='-03:00';

Select dbtimezone,sessiontimezone from dual;

Insert into date_table values (LOCALTIMESTAMP,LOCALTIMESTAMP );

Insert into date_table values (CURRENT_TIMESTAMP ,SYSTIMESTAMP);

COMMIT;