Datatime Datatypes in Oracle

In Oracle, there are four built-in datetime datatypes, they are:

• DATE - Stores a date and time, resolved to the second. Does not include time zone.
• TIMESTAMP - Stores a date and time without respect to time zone. Except for being able to resolve time to the billionth of a second (nine decimal places of precision), TIMESTAMP is the equivalent of DATE.
• TIMESTAMP WITH TIME ZONE - Stores the time zone along with the date and time value, allowing up to nine decimal places of precision.
• TIMESTAMP WITH LOCAL TIME ZONE - Stores a date and time with up to nine decimal places of precision. This datatype is sensitive to time zone differences. Values of this type are automatically converted between the database time zone and the local (session) time zone. When values are stored in the database, they are converted to the database time zone, but the local (session) time zone is not stored. When a value is retrieved from the database, that value is converted from the database time zone to the local (session) time zone.

 TIMESTAMP and DATE vary in formats as follows:

• DATE stores values as century, year, month, date, hour, minute, and second.
• TIMESTAMP stores values as year, month, day, hour, minute, second, and fractional seconds.

DATE in Oracle returns month, day, year, century, hours, minutes, and seconds. For more granular details, TIMESTAMP should be used. TIMESTAMP also returns fraction of seconds that helps to identify which event occurred first.

Example:

DATE: 16-DEC-08

TIMESTAMP: 16-DEC-08 12.12.23.000000000 PM

Declaring Datetime Variables

Use the following syntax to declare a datetime variable:

var_name [CONSTANT] datetime_type [{:= | DEFAULT} initial_value]

Replace datetime_type with any one of the following:

DATE

TIMESTAMP [(precision)]

TIMESTAMP [(precision)] WITH TIME ZONE

TIMESTAMP [(precision)] WITH LOCAL TIME ZONE

The precision in these declarations refers to the number of decimal digits allocated for recording values to the fraction of a second. The default precision is 6, which means that you can track time down to 0.000001 seconds. The allowable range for precision is 0 through 9, giving you the ability to store very precise time-of-day values.

Choosing a Datetime Datatype

It depends on the level of detail that you want to store when you choose a datetime datatype to use:

• User Date type if you only need to track time till to second.
• Use one of the TIMESTAMP types if you need to track time down to a fraction of a second.
• Use TIMESTAMP WITH LOCAL TIME ZONE if you want the database to automatically convert a time between the database and session time zones.
• Use TIMESTAMP WITH TIME ZONE if you need to keep track of the session time zone in which the data was entered.
• You can use TIMESTAMP in place of DATE. A TIMESTAMP that does not contain subsecond precision takes up 7 bytes of storage, just like a DATE datatype does. When your TIMESTAMP does contain subsecond data, it takes up 11 bytes of storage.