Date and Time Information
DATE is an Oracle data type that holds date and time information, up to an accuracy of seconds.
In Oracle 9i new data types were introduced to hold fractional seconds:
TIMESTAMP:- optinally specify the precision of fractional seconds.
TIMESTAMP WITH TIME ZONE:- same as above but includes time zone information (extra bytes).
TIMESTAMP WITH LOCAL TIME ZONE:- same as above but normalises time to database time zone.
INTERVAL YEAR TO MONTH:- stores length of time, specified in years and months.
INTERVAL DAY TO SECOND:- stores length of time specified in days, hours, minutes, seconds
FUNCTIONS
SYSDATE is an SQL function that returns the system date and time (to an accuracy of seconds).
CURRENT_DATE, session date and time in DATE format.
LOCALTIMESTAMP, session date and time in TIMESTAMP format (includes fractional seconds).
SYSTIMESTAMP, server date and time in TIMESTAMP format with TIMEZONE.