Introduction to Oracle TIMESTAMP WITH TIME ZONE Data Type

In Oracle Database, TIMESTAMP WITH TIME ZONE is a data type used to store date, time, and time zone information. This data type allows for precision up to sub-seconds and enables calculations and comparisons in any time zone.

Syntax

The syntax for TIMESTAMP WITH TIME ZONE is as follows:

TIMESTAMP WITH TIME ZONE

Use Cases

TIMESTAMP WITH TIME ZONE is commonly used for storing date and time information that needs to consider time zones, such as activity times recorded in different time zones, flight times, bank transaction times, etc. When using this data type, it is important to ensure that all time values are stored in Coordinated Universal Time (UTC).

Examples

Here are two examples of using the TIMESTAMP WITH TIME ZONE data type:

Example 1

Create a table named orders with columns for order information and order submission time, where the time information is stored as TIMESTAMP WITH TIME ZONE data type.

CREATE TABLE orders (
  order_id NUMBER,
  order_info VARCHAR2(100),
  order_time TIMESTAMP WITH TIME ZONE
);

Insert an order into the table with an order ID, order details, and order submission time using the TO_TIMESTAMP_TZ function.

INSERT INTO orders (order_id, order_info, order_time)
VALUES (1, 'apple watch', TO_TIMESTAMP_TZ('2023-04-03 10:30:00 -08:00', 'YYYY-MM-DD HH:MI:SS TZH:TZM'));

Query the data from the table and use the AT TIME ZONE keyword to convert the time information to a different time zone.

SELECT order_id, order_info, order_time, order_time AT TIME ZONE 'America/New_York' AS order_time_ny
FROM orders;

The result of the query would be:

ORDER_ID  ORDER_INFO    ORDER_TIME                              ORDER_TIME_NY
--------  ------------  -------------------------------------  -------------------------------------
       1  apple watch   03-APR-23 10.30.00.000000000 AM -08:00   03-APR-23 01.30.00.000000000 PM -04:00

Example 2

CREATE TABLE appointment (
   id NUMBER PRIMARY KEY,
   start_time TIMESTAMP WITH TIME ZONE,
   end_time TIMESTAMP WITH TIME ZONE
);

INSERT INTO appointment (id, start_time, end_time)
VALUES (1, TIMESTAMP '2023-04-03 08:30:00 Asia/Shanghai', TIMESTAMP '2023-04-03 10:00:00 Asia/Shanghai');

INSERT INTO appointment (id, start_time, end_time)
VALUES (2, TIMESTAMP '2023-04-04 10:00:00 America/New_York', TIMESTAMP '2023-04-04 12:00:00 America/New_York');

The above example creates a table named appointment with three columns: id, start_time, and end_time. Two rows are inserted into the table, representing appointments in the Asia/Shanghai time zone from 8:30 to 10:00 and in the America/New_York time zone from 10:00 to 12:00.

Conclusion

In Oracle Database, the TIMESTAMP WITH TIME ZONE data type allows for storing and processing date and time data with time zone information. It provides a convenient way to store and handle date and time data in different time zones, including time zone conversion and calculations.

The TIMESTAMP WITH TIME ZONE data type allows us to conveniently store and manipulate date and time data in different time zones, which is very useful for globalized applications. Additionally, Oracle database provides rich date and time functions such as EXTRACT, TO_CHAR, TO_DATE, etc., which make it easy to handle and manipulate data of type TIMESTAMP WITH TIME ZONE.

In summary, TIMESTAMP WITH TIME ZONE is a powerful and flexible data type that helps us store and manipulate date and time data in different time zones, making it an important data type in Oracle database.