What Is the Data Type for Time in Oracle?

What Is the Data Type for Time in Oracle?

In Oracle, the data type used to store time values is TIMESTAMP. The TIMESTAMP data type allows you to store both date and time information together. This data type includes fractional seconds and can be used to represent time values with great precision.

Using the TIMESTAMP Data Type

To use the TIMESTAMP data type in Oracle, you need to specify the precision of the fractional seconds when defining a column. The syntax for creating a table with a TIMESTAMP column is as follows:

CREATE TABLE table_name (
    column_name TIMESTAMP(precision)
);

Here, column_name is the name of your column, and precision specifies the number of digits in fractional seconds. The maximum value for precision is 9.

Example:

Let’s consider an example where we create a table called employees with a column named hire_date, which stores the hire date and time of employees:

CREATE TABLE employees (
    hire_date TIMESTAMP(6)
);

In this example, we have specified a precision of 6 for the fractional seconds.

Inserting Time Values into a TIMESTAMP Column

To insert time values into a TIMESTAMP column, you can use the TO_TIMESTAMP function or provide the value directly in an INSERT statement.

Using TO_TIMESTAMP Function:

The TO_TIMESTAMP function converts a string expression representing a date and/or time into a TIMESTAMP value. Here’s an example:

INSERT INTO employees (hire_date)
VALUES (TO_TIMESTAMP('2021-07-15 09:30:00', 'YYYY-MM-DD HH24:MI:SS'));

This statement inserts a specific date and time value into the hire_date column.

Providing Value Directly:

Alternatively, you can provide the value directly in the INSERT statement. Make sure to enclose the value within single quotes and use the format ‘YYYY-MM-DD HH24:MI:SS’:

INSERT INTO employees (hire_date)
VALUES ('2021-07-15 09:30:00');

Both methods will insert the same time value into the hire_date column.

Retrieving Time Values from a TIMESTAMP Column

When retrieving time values from a TIMESTAMP column, you can use various Oracle functions to extract specific parts of the timestamp.

Extracting Date and Time Separately:

To extract the date and time separately, you can use the EXTRACT function along with the appropriate format specifier. For example:

SELECT EXTRACT(YEAR FROM hire_date) AS year,
       EXTRACT(MONTH FROM hire_date) AS month,
       EXTRACT(DAY FROM hire_date) AS day,
       EXTRACT(HOUR FROM hire_date) AS hour,
       EXTRACT(MINUTE FROM hire_date) AS minute,
       EXTRACT(SECOND FROM hire_date) AS second
FROM employees;

This query will return each part of the hire_date separately.

Formatting Time Values:

If you want to format the time values in a specific way when retrieving them, you can use the TO_CHAR function. Here’s an example:

SELECT TO_CHAR(hire_date, 'YYYY-MM-DD HH24:MI:SS') AS formatted_date
FROM employees;

This query will return the hire_date in the format ‘YYYY-MM-DD HH24:MI:SS’.

Conclusion

In Oracle, the TIMESTAMP data type is used to store time values. It allows you to store both date and time information, including fractional seconds. By using appropriate functions, you can insert and retrieve time values effectively from a TIMESTAMP column in Oracle.