Introduction to Oracle TIMESTAMP Data Type

The Oracle TIMESTAMP data type is a data type used for storing dates and times. It can store timestamps accurate to nanoseconds and supports time zone conversion. The TIMESTAMP data type is widely used in database applications for storing timestamps, recording data modification times, and handling time zone conversions.

Syntax

The syntax for the TIMESTAMP data type is as follows:

TIMESTAMP [ (fractional_seconds_precision) ] [ WITH TIME ZONE | WITH LOCAL TIME ZONE ]

Where fractional_seconds_precision represents the precision, which can be a number from 0 to 9. WITH TIME ZONE indicates a timestamp with time zone, and WITH LOCAL TIME ZONE indicates a timestamp in the local time zone.

Use Cases

The TIMESTAMP data type is primarily used for storing timestamps and is suitable for the following use cases:

Storing data modification time: The TIMESTAMP data type can be used to record the modification time of data, facilitating data traceability and management.
Handling time zone conversion: The TIMESTAMP data type supports time zone conversion, making it easy to handle timestamps across different time zones.
Storing precise timestamps: The TIMESTAMP data type supports storing timestamps accurate to nanoseconds, making it suitable for scenarios that require high-precision timestamps.

Examples

Example 1: Storing data modification time

Create a table employees with fields id, name, and update_time, where the update_time field stores the modification time of data:

CREATE TABLE employees (
  id   NUMBER(10),
  name VARCHAR2(50),
  update_time TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP ON UPDATE
);

Insert a record into the employees table and update the data:

INSERT INTO employees (id, name) VALUES (1, 'John');
UPDATE employees SET name = 'Mike' WHERE id = 1;

Query the employees table, and you can see that the update_time field records the modification time of the data:

SELECT * FROM employees;

Output:

ID   NAME   UPDATE_TIME
---- ------ -----------------------
1    Mike   2023-04-04 09:47:42.808

Example 2

Assume there is a table orders that contains order information and an order date field. Now, we need to query all orders within a certain time range. We can use BETWEEN to filter.

SELECT *
FROM orders
WHERE order_date BETWEEN TIMESTAMP '2022-01-01 00:00:00.000'
  AND TIMESTAMP '2022-01-31 23:59:59.999';

The above statement will return all order records for January 2022.

You can use the TO_TIMESTAMP function to convert string-type dates into TIMESTAMP type. For example:

SELECT *
FROM orders
WHERE order_date BETWEEN TO_TIMESTAMP(
    '2022-01-01 00:00:00.000',
    'YYYY-MM-DD HH24:MI:SS.FF3'
  ) AND TO_TIMESTAMP(
    '2022-01-31 23:59:59.999',
    'YYYY-MM-DD HH24:MI:SS.FF3'
  );

This statement has the same effect as the previous statement, but it uses the TO_TIMESTAMP function for date string conversion.

The above two examples demonstrate the application of the TIMESTAMP type in queries.

Conclusion

TIMESTAMP data type is one of the commonly used date and time types in Oracle. It can store year, month, day, hour, minute, second, and nanosecond, providing timestamp accuracy up to milliseconds. In practical applications, TIMESTAMP type can be used to store time information and combined with various date and time functions and operators to perform calculations, comparisons, formatting, and other operations on time.