TIMESTAMP data type

 The Oracle TIMESTAMP data type is used to store date and time values with greater precision than the DATE data type. Unlike DATE, which stores time with second precision, TIMESTAMP provides fractional seconds (milliseconds, microseconds, or even nanoseconds depending on configuration). This makes the TIMESTAMP data type suitable for applications that require high-precision timekeeping.

Key Points of the Oracle TIMESTAMP Data Type:

1. Definition of TIMESTAMP

• Oracle TIMESTAMP is used to store date and time values with fractional seconds precision.
• It stores the same components as the DATE data type (i.e., year, month, day, hour, minute, second), but with an additional fractional seconds component.
• By default, the TIMESTAMP data type provides precision to 6 digits for fractional seconds (i.e., microseconds). You can also configure the precision to other levels such as 3 digits (milliseconds) or 9 digits (nanoseconds).

2. Syntax

The basic syntax for using the TIMESTAMP data type in a table definition is:

CREATE TABLE table_name (

    column_name TIMESTAMP

);

You can also define the precision for fractional seconds:

CREATE TABLE table_name (

    column_name TIMESTAMP(3)  -- 3 digits for milliseconds precision

);

3. Range of the TIMESTAMP Data Type

The TIMESTAMP data type supports a wide range of dates:

• Earliest date: January 1, 4712 BC (Julian calendar).
• Latest date: December 31, 9999 AD.

The TIMESTAMP data type also supports fractional seconds with the following precision options:

• 1 to 9 digits for fractional seconds, depending on the defined precision.

4. Internal Storage

Internally, Oracle stores a TIMESTAMP value as an 8-byte value (compared to the 7-byte value for DATE):

• The first 4 bytes store the date and time components (year, month, day, hour, minute, and second).
• The next 4 bytes store the fractional seconds (the precision depends on the defined fractional digits, for example, milliseconds or microseconds).

5. Timestamp Precision

The precision of the fractional second is controlled by the number inside the parentheses. The syntax is as follows:

TIMESTAMP(n)

Where n is the number of digits to store for fractional seconds. The valid values for n range from 0 to 9:

• TIMESTAMP(0): No fractional seconds.
• TIMESTAMP(3): Millisecond precision.
• TIMESTAMP(6): Microsecond precision.
• TIMESTAMP(9): Nanosecond precision.

6. Default Precision

If no precision is specified, the default precision is 6 digits for fractional seconds (i.e., microseconds).

Example:

CREATE TABLE log_events (

    event_time TIMESTAMP(3) -- stores time with millisecond precision

);

7. How TIMESTAMP is Used

• Inserting Data: You can insert a TIMESTAMP value using the TO_TIMESTAMP function, which converts a string to the TIMESTAMP data type:

INSERT INTO log_events (event_time)

VALUES (TO_TIMESTAMP('2025-02-24 12:30:45.123', 'YYYY-MM-DD HH24:MI:SS.FF3'));

The .FF3 in the format model indicates that you are specifying 3 digits of fractional seconds (milliseconds).

• Querying Data: You can retrieve TIMESTAMP values just like DATE values:

SELECT event_time FROM log_events;

8. Date and Time Functions with TIMESTAMP

Oracle provides several functions to work with TIMESTAMP values:

• SYSDATE: Returns the current date and time (but without fractional seconds).
• CURRENT_TIMESTAMP: Returns the current date and time with time zone and fractional seconds.
• SYSTIMESTAMP: Returns the system's current TIMESTAMP value with time zone information.

Example:

SELECT CURRENT_TIMESTAMP FROM dual; -- Includes fractional seconds

• EXTRACT: You can use EXTRACT to retrieve parts of a TIMESTAMP:

SELECT EXTRACT(HOUR FROM event_time) FROM log_events;

9. Working with TO_TIMESTAMP and TO_CHAR

• TO_TIMESTAMP: Converts a string into a TIMESTAMP value. This function can handle fractional seconds if provided in the format model.

·        SELECT TO_TIMESTAMP('2025-02-24 12:30:45.123', 'YYYY-MM-DD HH24:MI:SS.FF3') FROM dual;

• TO_CHAR: Converts a TIMESTAMP value into a formatted string. You can specify the desired format for the TIMESTAMP:

·        SELECT TO_CHAR(event_time, 'YYYY-MM-DD HH24:MI:SS.FF3') FROM log_events;

10. Comparing TIMESTAMP Values

You can use standard comparison operators to compare TIMESTAMP values:

SELECT * FROM log_events

WHERE event_time > TO_TIMESTAMP('2025-02-24 12:30:45.123', 'YYYY-MM-DD HH24:MI:SS.FF3');

11. Using TIMESTAMP with TIMESTAMP WITH TIME ZONE

While TIMESTAMP handles date and time values with fractional seconds, it does not store time zone information. If you need to store time zone information along with the date and time, you should use TIMESTAMP WITH TIME ZONE.

Example:

CREATE TABLE events_with_timezone (

    event_time TIMESTAMP WITH TIME ZONE

);

This will allow you to store a time zone along with the date and time, which is important for applications working across multiple time zones.

12. When to Use TIMESTAMP

The TIMESTAMP data type is ideal when you need precise time data and fractional second precision for tasks like:

• Logging events with millisecond or microsecond accuracy.
• Storing timestamps for financial transactions, system logs, or scientific data that require high precision.
• Tracking time-sensitive applications where milliseconds or finer granularity is required.

13. Differences Between TIMESTAMP and DATE

• Precision: TIMESTAMP has fractional seconds precision, while DATE only stores up to second-level precision.
• Storage: TIMESTAMP takes up more space (8 bytes) compared to DATE (7 bytes), due to the additional fractional seconds component.
• Use Cases: DATE is used when only date and time up to seconds precision are required, whereas TIMESTAMP is used for high-precision timekeeping.

14. Example Use Case: Storing Log Events with Precise Time

CREATE TABLE log_events (

    log_id NUMBER,

    event_time TIMESTAMP(6), -- Store time with microsecond precision

    event_description VARCHAR2(255)

);

 

-- Inserting data

INSERT INTO log_events (log_id, event_time, event_description)

VALUES (1, TO_TIMESTAMP('2025-02-24 15:30:45.123456', 'YYYY-MM-DD HH24:MI:SS.FF6'), 'Event 1');

 

-- Querying data

SELECT * FROM log_events;

15. Considerations When Using TIMESTAMP

• Storage: TIMESTAMP uses more storage space than DATE. Ensure you need the fractional second precision before using TIMESTAMP in all columns.
• Time Zones: Use TIMESTAMP WITH TIME ZONE if you need time zone support.
• Performance: In most cases, the performance difference between DATE and TIMESTAMP is negligible. However, for extremely high-volume applications, consider the storage size and indexing impact.

 

Conclusion:

The Oracle TIMESTAMP data type is powerful and provides high precision for storing date and time values, especially in systems where fractional second precision is required. Whether you are working with logging events, transaction times, or other time-sensitive data, TIMESTAMP allows you to store and manipulate date-time information with precision beyond what the standard DATE data type offers.

Feel free to ask if you need further details or examples!