Oracle Timestamp Datatype

by
use the timestamp datatype in sql developer tool

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);
using the timestamp datatype by query in oracle
using the timestamp datatype by query in oracle

Read: How to drop database in Oracle

Example

Now we will use the timestamp datatype in a SQL developer tool.

use the timestamp datatype in sql developer tool
use the timestamp datatype in 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'));
insert values into customer table
insert values into the customer table

Also, check: Oracle Create Sequence Tutorial

Conclusion

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.