What Is TIMESTAMP Data Type in Oracle?

What Is TIMESTAMP Data Type in Oracle?

The TIMESTAMP data type in Oracle is used to store both date and time information. It is a useful data type when you need to record precise timestamps for events or track the timing of transactions. The TIMESTAMP data type stores information down to the fractional second, allowing for high precision and accuracy.

Working with TIMESTAMP Data Type

To work with the TIMESTAMP data type in Oracle, you can use various functions and operators to manipulate and format the values as per your requirements. Here are a few commonly used ones:

1. INSERTING TIMESTAMP Values

When inserting a value into a column with the TIMESTAMP data type, you can either provide a specific timestamp or use built-in functions like CURRENT_TIMESTAMP or SYSTIMESTAMP. For example:

To insert the current timestamp:

INSERT INTO my_table (timestamp_column) VALUES (CURRENT_TIMESTAMP);

To insert the system timestamp:

INSERT INTO my_table (timestamp_column) VALUES (SYSTIMESTAMP);

To insert a specific timestamp value:

INSERT INTO my_table (timestamp_column) VALUES ('2022-01-01 12:34:56');

2. EXTRACTING Components from TIMESTAMP Values

You can extract various components from a TIMESTAMP value using functions like EXTRACT. For example:

To extract the year:

SELECT EXTRACT(YEAR FROM timestamp_column) FROM my_table;

To extract the month:

SELECT EXTRACT(MONTH FROM timestamp_column) FROM my_table;

To extract the day:

SELECT EXTRACT(DAY FROM timestamp_column) FROM my_table;

3. Formatting TIMESTAMP Values

You can format a TIMESTAMP value using the TO_CHAR function to display it in a specific format. For example:

To display the timestamp in ‘YYYY-MM-DD HH24:MI:SS’ format:

SELECT TO_CHAR(timestamp_column, 'YYYY-MM-DD HH24:MI:SS') FROM my_table;

To display only the date part of the timestamp:

SELECT TO_CHAR(timestamp_column, 'YYYY-MM-DD') FROM my_table;

Conclusion

The TIMESTAMP data type in Oracle provides a convenient way to store and manipulate date and time information with high precision. By using functions and operators, you can easily work with and format TIMESTAMP values as per your requirements.