Oracle PL/SQL Programming, 5th Edition

Datetime Datatypes

For a long time, the only datetime datatype available was DATE. Oracle9i Database shook things up by introducing three new TIMESTAMP and two new INTERVAL datatypes offering significant, new functionality while also bringing Oracle into closer compliance with the ISO SQL standard. I’ll talk more about the INTERVAL datatypes later in this chapter. The four datetime datatypes are:

DATE

Stores a date and time, resolved to the second. Does not include time zone.

TIMESTAMP

Stores date and time without respect to time zone. Except for being able to resolve time to the billionth of a second (9 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 9 decimal places of precision.

TIMESTAMP WITH LOCAL TIME ZONE

Stores a date and time with up to 9 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.

The nuances of these types, especially the TIMESTAMP WITH LOCAL TIME ZONE type, can be a bit difficult to understand at first. To help illustrate, let’s look at the use of TIMESTAMP ...