10.3. Using Datetime Datatypes

A challenge in managing data in a global environment is synchronizing transactions that occur across time zones. Oracle's globalization support offers special datatypes and functionality to manage dates and times across differing time zones.

In the following sections, you'll learn about the Oracle datatypes that store date and time information. The data stored using these datatypes are often called datetimes, and you'll learn about the following:

• DATE

• TIMESTAMP

• TIMESTAMP WITH TIME ZONE

• TIMESTAMP WITH LOCAL TIME ZONE

You'll also be introduced to several datetime SQL functions. Lastly, time zone parameters and files will be covered.

10.3.1. DATE Datatype

The DATE datatype is used to store date information as well as time information in the database. In fact, every date stored as a DATE datatype will have an accompanying time, even if no time was specified when the date was stored. See the following sidebar "Time Elements in DATE Datatypes" for further information on time elements.

To define a column using the DATE datatype, use the DATE keyword as shown here:

SQL> create table birthdates (
  client_id NUMBER,
  birthdate DATE);

Table created.

Oracle dates consist of seven parts: century, year, month, day, hours, minutes, and seconds (elapsed since midnight). In fact, they are stored internally in the database as seven separate elements.

To demonstrate, insert a row into the BIRTHDATES table that was created earlier:

SQL> insert into birthdates
  2 values(1, sysdate);

1 row created.

SQL> commit;

Commit complete.

Next, select the date from the table using the following TO_CHAR formatting option:

SQL> select to_char(birthdate,'YYYY-MM-DD:HH24:MI:SS')
  2  from birthdates;

TO_CHAR(BIRTHDATE,'
-------------------
2004-10-22:11:29:44

You can see the elements displayed clearly when using this formatting option. However, that doesn't tell you anything about how the data is stored internally. To see that, use the DUMP function, as shown here:

SQL> select dump(birthdate)
  2  from birthdates;

DUMP(BIRTHDATE)
------------------------------------------------------
Typ=12 Len=7: 120,104,10,22,12,30,45

The DUMP function shows the datatype, length (number of bytes), and the actual byte values for a particular element. So, the example shows that the BIRTHDATE element is stored internally as a DATE datatype (typ=12). It occupies seven bytes (Len=7) of storage, and the values stored in those bytes are: 120, 104, 10, 22, 12, 30, and 45.

Oracle stores the century and the year elements using excess 100 notation, which means that it adds 100 to the number before storing it. Also, the hours, minutes, and seconds elements are stored using excess 1 notation. As you probably guessed, that means that it adds one to each number before storing it. Therefore, if the stored values were converted to standard decimal notation, this is what we would see:

DUMP(BIRTHDATE)
------------------------------------------------------
Typ=12 Len=7: 20,04,10,22,11,29,44

Now, you can see that the stored values do indeed match the original date, as shown here:

SQL> select to_char(birthdate,'YYYY-MM-DD:HH24:MI:SS')
  2  from birthdates;

TO_CHAR(BIRTHDATE,'
-------------------
2004-10-22:11:29:44

By better understanding how the data is stored internally, you can think of dates as collections of individual elements that can be accessed together or individually.

SQL> select sysdate from dual;

SYSDATE
----------
09-02-2004

SQL> insert into conv_dates
  values ('09-02-2004', to_date('09-02-2004','MM-DD-YYYY'));

1 row created.

SQL> commit;

Commit complete.

SQL> select * from conv_dates where converted_date = sysdate;

no rows selected

SQL> select
  to_char(converted_date, 'MM-DD-YYYY HH24:MI')
  from conv_dates;

TO_CHAR(CONVERTE
----------------
12-07-1941 00:00
06-06-1944 00:00
09-02-2004 00:00

SQL> select
  to_char(sysdate,'MM-DD-YYYY HH24:MI')
  from dual;

TO_CHAR(SYSDATE,
----------------
09-02-2004 16:47

SQL> select * from conv_dates
  where trunc(converted_date) = trunc(sysdate);

DATESTRING      CONVERTED_
--------------- ----------
09-02-2004      09-02-2004

When entering date information into a DATE datatype, you can specify it in several ways:

Literal The date can be entered as a literal, which matches the NLS_DATE_FORMAT. For example, if NLS_DATE_FORMAT is defined as 'MM-DD-YYYY', then a literal of '12-30-1972' would be acceptable. For example:

SQL> alter session set NLS_DATE_FORMAT = "MM-DD-YYYY";

Session altered.

SQL> insert into birthdates
values(2, '12-30-1972');

1 row created.

Note that in this example, because no time portion was specified, Oracle will set the time elements to represent midnight.

ANSI date literal An ANSI (American National Standards Institute) date literal contains no time element. It must be formatted exactly as shown here:

DATE 'YYYY-MM-DD'

Dates can be entered using the ANSI date literal format at any time, regardless of the NLS_DATE_FORMAT setting. For example:

SQL> insert into birthdates
  2  values(3, DATE '1966-08-25'),

1 row created.

TO_DATE function Dates can also be entered by using the TO_DATE function. This function converts text strings to DATE types based on the format specified. An example is shown here:

SQL> insert into birthdates
  values(4,
  to_date('09-29-1993 13:45', 'MM-DD-YYYY HH24:MI');

This example specifies not only the date, but also the hours and minutes elements of the time.

The TO_DATE function can also be handy for converting dates that have been stored as character types. Consider the following examples:

SQL> create table conv_dates (
  datestring varchar2(15),
  converted_date date);

Table created.

This table will hold a string of 15 characters, and a date. Now, a string representation of two dates will be inserted in the table. Note that they are being stored as VARCHAR2 character data, not as dates.

SQL> insert into conv_dates (datestring)
  values ('08-25-2000');

1 row created.

SQL> insert into conv_dates (datestring)
  values ('07-25-2001');

1 row created.

SQL> commit;
Commit complete.

Next, the strings will be converted and stored as dates by using the TO_DATE function:

SQL> update conv_dates
  set converted_date =
  to_date(datestring,'MM-DD-YYYY');

2 rows updated.

SQL> commit;

Commit complete.

Now, both will be selected, as shown here:

SQL> select * from conv_dates;

DATESTRING      CONVERTED_D
--------------- -----------
08-25-2000      25-AUG-2000
07-25-2001      25-JUL-2001

As you can see in this example, the TO_DATE function converted the dates stored as VARCHAR2 data in the DATESTRING column into the DATE datatype format, which was subsequently stored in the CONVERTED_DATE column.

10.3.2. TIMESTAMP Datatype

The TIMESTAMP datatype offers all the date and time elements found in the DATE datatype, but offers the extended functionality of storing fractional seconds.

By default, the TIMESTAMP datatype stores fractional seconds to six digits of precision. This can be changed, however, by specifying a number between 0 and 9 in parentheses after the TIMESTAMP keyword. This number determines the digits of precision for the fractional seconds, as shown here:

SQL> create table test_stamp (stamp timestamp(2));

Table created.

SQL> insert into test_stamp
select to_timestamp('09-SEP-2004 17:54.38.92',
'DD-MON-YYYY HH24:MI:SS:FF')
from dual;

1 row created.

SQL> commit;
Commit complete.

SQL> select * from test_stamp;

STAMP
------------------------------------
09-SEP-04 05.54.38.92 PM

As you can see, the timestamp was entered using the TO_TIMESTAMP function (which is similar to the TO_DATE function). Notice that fractional seconds can be specified by using the FF element in the date mask.

The TIMESTAMP datatype should be used when locale information (time zone) is not required, but fractional second granularity is. For example, application event logging is a common use of the TIMESTAMP datatype.

10.3.3. TIMESTAMP WITH TIME ZONE Datatype

The TIMESTAMP WITH TIME ZONE datatype extends the functionality of the TIMESTAMP datatype by including time zone information. The time zone data is stored as an offset (hours and minutes) between the local time and the UTC (Coordinated Universal Time, formerly known as Greenwich Mean Time). It can be displayed either in this form or in the form of a region name.

Like the TIMESTAMP datatype, the TIMESTAMP WITH TIME ZONE datatype stores fractional seconds to six digits of precision. This can be changed by specifying a number between 0 and 9 in parentheses between the TIMESTAMP keyword and the WITH TIME ZONE keywords. This number determines the digits of precision for the fractional seconds. For example:

SQL> create table stamp_tz (stamp_tz TIMESTAMP(4) WITH TIME ZONE);
Table created.

The TIMESTAMP WITH TIME ZONE is recommended when local information and precise time transactions across time zones need to be synchronized. For example, a bank with branches in different time zones needs to post transactions in real time, regardless of location.

10.3.4. TIMESTAMP WITH LOCAL TIME ZONE Datatype

The TIMESTAMP WITH TIME ZONE doesn't actually store time zone information at all. Instead, when a record is inserted into a column defined with a datatype of TIMESTAMP WITH LOCAL TIME ZONE, the following happens:

• If the incoming data has no time zone element, it is assumed to be local time and is stored as is.

• If the incoming data has a time zone element but the time zone matches the local time zone, the time zone element is dropped and the data is stored.

• If the incoming data has a time zone element and the time zone does not match the local time zone, the timestamp is adjusted to local time. The data is then stored without a time zone element.

Using this method synchronizes all time elements to the local time, allowing a company that spans multiple time zones to see data in real time relative to the local time zone.

For example, suppose your company is headquartered in London and you have a branch office in New York. Transactions from the branch office need to be stored in your London database and synchronized to London time.

The time zone information for London is as follows:

The time zone information for New York is as follows:

As you can see, London is currently one hour ahead of UTC, whereas New York is four hours behind. Therefore, London's time is five hours ahead of New York's.

Now, suppose a transaction comes in from your New York branch to your London office.

The timestamp data will be stored in a column defined with the TIMESTAMP WITH LOCAL TIME ZONE datatype. Before storing the data, Oracle synchronizes the time by adding five hours to the timestamp value and drops the time zone element.