Use timestamps : Timestamp « SQL Data Types « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. SQL Data Types
3. Timestamp

Timestamps store a specific date and time.

A timestamp stores the century, all four digits of a year, the month, the day, the hour (in 24-hour format), the minute, and the second.

The timestamp can store a fractional second, DATE cannot.

Timestamp can store a time zone.

A timestamp stores

1. the century,
2. all four digits of a year,
3. the month,
4. the day,
5. the hour (in 24-hour format),
6. the minute, and
7. the second.

The advantages of a timestamp over a DATE are

1. A timestamp can store a fractional second.
2. A timestamp can store a time zone.

There are three timestamp types

TIMESTAMP[(seconds_precision)] Stores the

1. century,
2. all four digits of a year,
3. the month,
4. the day,
5. the hour (in 24-hour format),
6. the minute, and
7. the second.

You can specify an optional precision for the seconds by supplying seconds_precision

The seconds_precision can be an integer from 0 to 9.

The default is 6; which means you can store up to 6 digits to the right of the decimal point for your second.

If you try to add a row with more digits in your fractional second than your TIMESTAMP can store, your fraction is rounded.

1. TIMESTAMP[(seconds_precision)] WITH TIME ZONE Extends TIMESTAMP to store a time zone.
2. TIMESTAMP[(seconds_precision)] WITH LOCAL TIME ZONE Extends TIMESTAMP to convert a supplied datetime to the local time zone set for the database. The process of conversion is known as normalizing the datetime.

Quote from:

Oracle Database 10g SQL (Osborne ORACLE Press Series) (Paperback)

# Paperback: 608 pages

# Publisher: McGraw-Hill Osborne Media; 1st edition (February 20, 2004)

# Language: English

# ISBN-10: 0072229810

# ISBN-13: 978-0072229813