In this Oracle tutorial, we will learn about the Timestamp datatype in the oracle database. Also, we will demonstrate how to use it to create Timestamp columns for a table in oracle.
Introduction to Oracle Timestamp Datatype
In Oracle 21c, the TIMESTAMP datatype is used to store date and time data in the database. The TIMESTAMP datatype holds both the date and the time of day, with an accuracy of up to fractional seconds, in comparison to the DATE datatype, which only saves date information.
This allows you to store more detailed information about when events occurred and make it easier to perform time-based operations in your queries.
Oracle Timestamp datatype Syntax
The syntax for declaring a column of TIMESTAMP datatype in Oracle 21c is as follows
column_name TIMESTAMP [(fractional_seconds_precision)]
where column_name is the name of the column, TIMESTAMP is the datatype, and fractional_seconds_precision is an optional parameter that specifies the number of fractional digits in the fractional seconds portion of the timestamp. If not specified, the default precision is 6 fractional digits.
Also, check: How to check database status in Oracle
Oracle Timestamp datatype Example
Here’s an example of how you can create a table with a TIMESTAMP column in Oracle 21c.
CREATE TABLE transactions ( transaction_id NUMBER, transaction_timestamp TIMESTAMP(6), transaction_amount NUMBER );
Now we will insert data into a table with a TIMESTAMP column using the INSERT statement.
INSERT INTO transactions (transaction_id, transaction_timestamp, transaction_amount) VALUES (1, TO_TIMESTAMP('2021-01-01 12:00:00.123456', 'YYYY-MM-DD HH24:MI:SS.FF'), 100);
Now we will use the timestamp datatype in a SQL developer tool.
Here we will insert the values into a customer table
INSERT INTO customers (customer_name, product_delivering_time) VALUES ('Micheal', TO_TIMESTAMP('2021-01-01 12:00:00.123456', 'YYYY-MM-DD HH24:MI:SS.FF'));
Also, check: Oracle Create Sequence Tutorial
So, in this Oracle tutorial, we understood how to define and use the timestamp 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.
- Date Datatype in Oracle Database
- Char Datatype in Oracle Database
- Oracle Varchar2 Datatype
- Nchar Datatype in Oracle Database
- NVARCHAR2 Datatype in Oracle Database
- Float Datatype in Oracle Database
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.