Mastering Oracle SQL

Oracle9i New DATETIME Features

With Oracle9i, Oracle introduced features to enhance support for temporal data. These new features form the basis of Oracle’s support for:

• Time zones

• Date and time data with fractional seconds

• Date and time intervals

In this section we discuss these enhancements and their uses.

Time Zones

In the Internet economy, business is carried out across geographical boundaries and time zones. Oracle facilitates global e-business through its support for time zones. With Oracle9i, a database and a session can now be associated with time zones. Having database and session time zones enables users in geographically distant regions to exchange temporal data with the database without having to bother about the time differences between their location and the server’s location.

Database time zone

We can set the time zone of a database when we create the database. After creating the database, we can change the time zone using the ALTER DATABASE command. Both CREATE DATABASE and ALTER DATABASE take an optional SET TIME_ZONE clause. Specify a time zone in one of the two ways:

• By specifying a displacement from the Coordinated Universal Time (UTC).

• By specifying a time zone region.

The displacement from the UTC is specified in hours and minutes with a + or - sign. Every time zone region is given a region name. For example, EST is the region name for Eastern Standard Time. We can also use such a region name to set the time zone of a database.

Tip

UTC was formerly known as Greenwich ...