Understanding Oracle Datetime Datatypes: DATE, TIMESTAMP, and INTERVAL

In this blog post, I’ll explore some fundamental concepts of Oracle’s datetime datatypes---specifically, the DATE, TIMESTAMP, and INTERVAL datatypes. Time-based data is at the core of many applications, from tracking transactions to logging events, so understanding how Oracle handles dates and times is crucial for managing and manipulating this kind of information effectively.

DATE and TIMESTAMP
Oracle used to have only one datatype for date and time, which was DATE. Starting with Oracle 9i, three new TIMESTAMP datatypes were introduced.

The four datetime datatypes:
DATE
TIMESTAMP
TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH LOCAL TIME ZONE

DATE
The DATE datatype stores a date and time, resolved to the second. It does not include time zone information. For each DATE value, Oracle stores century, year, month, date, hour, minute, and second.

You can specify a DATE value by:

• Specifying the date value as a literal.
• Converting a character or numeric value to a date value with the TO_DATE function.

Examples:

-- Literal (’YYYY-MM-DD’):
DATE ‘1995-10-31’

-- Oracle date values:
TO_DATE(’1995-OCT-31 15:30’, ‘YYYY-MON-DD HH24:MI’, ‘NLS_DATE_LANGUAGE=AMERICAN’)

TIMESTAMP
The TIMESTAMP datatype is an extension of the DATE datatype. It stores year, month, date, hour, minute, and second values. Additionally, it stores fractional seconds, which the DATE datatype does not.
The TIMESTAMP datatype does not store time zone information.

_TIMESTAMP [(fractional_seconds_precision)] _

Where fractional_seconds_precision is optional and specifies the number of digits (0 to 9). The default is 6.

Example

--TIMESTAMP literal (TIMESTAMP ‘YYYY-MM-DD HH24:MI:SS.FF’):
TIMESTAMP ‘1995-10-31 12:34:56.78’

TIMESTAMP WITH TIME ZONE
The TIMESTAMP WITH TIME ZONE datatype stores the time zone along with each date and time value. This makes it possible to manage date and time values in different time zones.

The time zone offset represents the difference between local time and UTC (Coordinated Universal Time).

TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE

Where fractional_seconds_precision is optional and specifies the number of decimal places for seconds.

Example:

TIMESTAMP ‘1995-10-31 12:34:56.78 +03:00’

TIMEZONE WITH LOCAL TIME ZONE
The TIMEZONE WITH LOCAL TIME ZONE datatype stores a date and time that is assumed to be in the local time zone. The data is normalized to the database’s time zone (the time zone of the server).

TIMESTAMP [(fractional_seconds_precision)] WITH LOCAL TIME ZONE

Where fractional_seconds_precision is optional and specifies the number of decimal places for seconds.

The difference between TIMESTAMP WITH TIME ZONE and TIMEZONE WITH LOCAL TIME ZONE can be explained in this example:

Let’s say we have an employee clock-in application. The company is based in Tokyo, the employee works in Tokyo, and the database server is also in Tokyo. The employee then goes on a business trip to New York and clocks in from there.

With TIMESTAMP WITH TIME ZONE, the clock-in time is stored in New York time (Eastern Time).

With TIMEZONE WITH LOCAL TIME ZONE, the clock-in time is always stored in Tokyo (Japan) time, regardless of the employee’s actual location.

Interval Datatypes
Oracle9i introduced Interval datatypes, which are used to record and compute quantities of time.

DATE and all TIMESTAMP datatypes represent instances of time (a specific point in time). INTERVAL datatypes, on the other hand, refer to a specific amount or duration of time.

There are two interval datatypes:

INTERVAL YEAR TO MONTH
The INTERVAL YEAR TO MONTH datatype stores a period of time in terms of years and months.

INTERVAL YEAR [(year_precision)] TO MONTH

Where year_precision specifies the number of digits in the YEAR field (from 0 to 9). The default is 2.

You can specify interval values with literals.

Example:

INTERVAL ‘12-3’ YEAR(2) TO MONTH

This specifies an interval of 12 years and 3 months.

INTERVAL DAY TO SECOND
The INTERVAL DAY TO SECOND datatype stores a period of time in days, hours, minutes, and seconds.

INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds_precision)]

day_precision specifies the number of digits in the DAY field. Accepted values are 0 to 9, with a default of 2.

fractional_seconds_precision specifies the number of digits in the fractional part of seconds. Accepted values are 0 to 9, with a default of 6.

Example:

INTERVAL ‘2 3:45:10.666’ DAY TO SECOND(3)

This specifies an interval of 2 days, 3 hours, 45 minutes, 10 seconds, and 666 thousandths of a second.

In short, Oracle’s DATE and TIMESTAMP datatypes store and manipulate time-related data, while the INTERVAL datatypes handle periods of time. However, this is just the beginning. In future blog posts, I’ll explore more about these datatypes and their uses.