Time Datatype in Oracle Database

In this Oracle tutorial, we will learn about the Time datatype in the oracle database. Also, we will demonstrate how to use it to create time columns for a table in Oracle.

Introduction to Oracle Time datatype

Oracle 21c uses the TIME data type to hold time values without a date component. Similar to the TIMESTAMP data type, the TIME data type exclusively holds time-related information without the date component.

Oracle Time datatype Syntax

Until now, we have understood the Time datatype in a database and discussed the purpose of the Time datatype. Next, we will take a step further and understand how to use it to create Time datatype columns for a table.

For this, we need to understand the syntax to use a Time datatype in oracle 21c which is given below.

column_name TIME [ (fractional_seconds) ];

Where fractional_seconds is an optional parameter that specifies the number of fractional seconds to store. The default is 6, but you can specify a value from 0 to 9.

Also, check: Oracle Create Index

Oracle time datatype Example

The Oracle time datatype is used to store time values (hours, minutes, and seconds) in a database. Here is an example of how you can use the time datatype in Oracle.

CREATE TABLE mytable (
    event_date DATE
);

Here we created a table ‘mytable’ using the column ‘event_date’.

INSERT INTO mytable (event_date) VALUES (TO_DATE('2022-01-01 14:30:00', 'YYYY-MM-DD HH24:MI:SS'));

Read: How to add a primary key in Oracle

Example:

Here we will create another example to use the time datatype in Oracle 21c

CREATE TABLE appointment (
  appointment_id NUMBER,
  customer_name VARCHAR2(50),
  appointment_time Date
);

The columns “appointment id” (number), “customer name” (varchar2), and “appointment time” comprise the table “appointment” (Date). You can use the SQL query below to add a new row to the table:

INSERT INTO appointment (appointment_id, customer_name, appointment_time)
VALUES (1, 'John Smith', TO_DATE('09:00:00', 'HH24:MI:SS'));

INSERT INTO appointment (appointment_id, customer_name, appointment_time)
VALUES (2, 'Jane Doe', TO_DATE('10:30:00', 'HH24:MI:SS'));

INSERT INTO appointment (appointment_id, customer_name, appointment_time)
VALUES (3, 'Bob Johnson', TO_DATE('13:45:00', 'HH24:MI:SS')); 

The “appointment time” columns in this example are defined as the Oracle TIME data type, a subtype of the DATE data type that exclusively holds the time component of a date and time value. To convert the string values, use the TO DATE function.

Read How to Check and Set Database Timezone in Oracle

Benefits of Oracle Time datatype

In Oracle, there is no standalone TIME data type. Instead, time information is included as part of the DATE data type. However, the DATE data type still provides several benefits for storing and working with time information.

• Accuracy: You can save time information with high accuracy using the DATE data type, down to fractions of a second.
• Flexibility: The DATE data type is a flexible data type for working with timestamps since it can store both date and time information.
• Built-in functions: To format, parse, and retrieve time information, Oracle offers a variety of built-in functions for working with DATE data types, including TO CHAR, TO DATE, and SYSDATE.
• Compatibility: The DATE data type is a dependable option for storing time information because it is widely supported and utilized in Oracle databases and applications.
• Support for timezone information is built-in to the Oracle DATE data type, making it simpler to work with date and time data across.
• Overall, the DATE data type in Oracle provides several benefits for storing and working with time information, including accuracy, flexibility, built-in functions, compatibility, and timezone support.

Advantages of Oracle Time datatype

There are several advantages of using a database management system (DBMS) like Oracle, including:

• Better data management and organization: A DBMS like Oracle offers a structured method of managing and organizing data. To guarantee data consistency and accuracy, you can define relationships between data pieces, create data integrity rules, and apply constraints using a DBMS.
• Enhanced data security: To guard against unwanted access, modification, or theft, DBMSs like Oracle offer a variety of security features like user authentication, access limits, and encryption.
• A DBMS like Oracle offers capabilities like backups, disaster recovery, and fault tolerance to ensure that data is always available and accessible, even in the case of system failures or disasters. This increases data availability and reliability.
• greater scalability and performance: Data structures, indexing techniques, and query optimization are all provided by DBMSs like Oracle.

Disadvantages of Oracle Time datatype

While there are many advantages to using a database management system like Oracle, there are also some potential disadvantages, including:

• Cost: As a DBMS like Oracle requires specialised hardware and software as well as trained staff to set up and administer the system, implementing and maintaining one can be costly.
• Complexity: DBMS systems like Oracle can be complicated, and configuring, optimising, and maintaining them requires a great deal of experience. The amount of time and resources needed to manage the system may rise as a result.
• Dependency: A DBMS, such as Oracle, can make the system dependent because applications may be created particularly to use the database. This can make it challenging to transition to a different technology platform or convert to a different DBMS in the future.
• Security hazards: Despite the security measures that a DBMS like Oracle offers, it is still susceptible to security threats including unauthorised access, hacking,

Conclusion:

So, in this Oracle tutorial, we understood how to define and use the Time Datatype in Oracle Database. And we have also covered a few sample examples related to it.

Also, take a look at some more Oracle tutorials.

• Oracle Binary_float Datatype
• Oracle numeric Datatype
• Real Datatype Oracle