Internet revolution has dramatically changed the way of conducting global business. Enterprises nowadays are constantly transacting business across boarders and time zones where each party involved has its own notion of  time. To help Oracle's customers conducting global E-Business, Oracle9i provides extensive support of datetime datatypes that are compliant with ANSI/OSI SQL99 standard.

Database Time Zone Parameter

• By specifying a displacement from UTC (Coordinated Universal Time¿formerly Greenwich Mean Time). The following example sets the time zone of the database to Pacific Standard time (8 hours earlier than UTC):

  CREATE DATABASE ... SET TIME_ZONE = '-08:00';

• By specifying a time zone region. A user can query the V$TIMEZONE_NAMES dynamic performance view to see a listing of valid region names. The following example also sets the time zone of the database to Pacific Standard time in the U.S.:

  CREATE DATABASE ... SET TIME_ZONE = 'PST';

Session Time Zone Parameter

• O/S Local Time Zone, for example,

  ALTER SESSION SET TIME_ZONE = local;

• Database Time Zone, for example,

  ALTER SESSION SET TIME_ZONE = dbtimezone;

Datetime Datatypes

Oracle supports the following timestamp datatypes:

• TIMESTAMP (fractional_seconds_ precision) Year, month, and day values of date, as well as hour, minute, and second values of time, where fractional_seconds_precision optionally specifies the number of digits in the fractional part of the SECOND datetime field and can be a number in the range 0 to 9. The default is 6. For example, you specify TIMESTAMP as a literal as follows:

  TIMESTAMP'1997-01-31 09:26:50.124'

• TIMESTAMP (fractional_seconds_precision) WITH TIME ZONE All values of TIMESTAMP as well as the time zone displacement value, where fractional_seconds_precision optionally specifies the number of digits in the fractional part of the SECOND datetime field and can be a number in the range 0 to 9. The default is 6. For example, you specify TIMESTAMP WITH TIME ZONE as a literal as follows:

  TIMESTAMP '1999-04-15 8:00:00 -8:00'

  This can also be specified as

  TIMESTAMP '1999-04-15 8:00:00 US/Pacific'

• TIMESTAMP (fractional_seconds_precision) WITH LOCAL TIME ZONE All values of TIMESTAMP, with the following exceptions:
  • Data is normalized to the database time zone when it is stored in the database.
  • When the data is retrieved, users see the data in the session time zone.

Interval Datatypes

• INTERVAL YEAR (year_ precision) TO MONTH Stores a period of time in years and months, where year_precision is the number of digits in the YEAR datetime field. Accepted values are 0 to 9. The default is 6.
• INTERVAL DAY (day_precision) TO SECOND (fractional_seconds_precision) Stores a period of time in days, hours, minutes, and seconds, where day_precision is the maximum number of digits in the DAY datetime field (accepted values are 0 to 9; the default is 2), and fractional_seconds_precision is the number of digits in the fractional part of the SECOND field. Accepted values are 0 to 9. The default is 6.

More Info

	Visit the OTN Discussion Forum that the Oracle SQL and PL/SQL teams monitors
	Oracle 9i SQL Reference documentation contains in-depth description of datetime datatypes

Oracle9i Database Daily Features

Archives