How to insert a timestamp in Oracle?

Question

I am having an Oracle database with a timestamp type field in it. I want to insert the timestamp data into that field. What will be the right SQL code(syntax) for inserting a timestamp into this field?

Answer 1

You can use the below code:

insert
into tablename (timestamp_value)
values (TO_TIMESTAMP(:ts_val, 'YYYY-MM-DD HH24:MI:SS'));

If you need the current timestamp to be inserted then use the following code: 

insert
into tablename (timestamp_value)
values (CURRENT_TIMESTAMP);

Willing to Learn SQL? Sign up for the SQL Certification course by Intellipaat.

Watch the below SQL video tutorial to gain more knowledge on SQL...

Answer 2

In Oracle, we have a keyword ‘TIMESTAMP’ which will help you to insert timestamps.

You can do it with reference to the below query:

INSERT INTO your_table_name (timestamp_column) 

VALUES (TO_TIMESTAMP('2024-08-28 10:30:00', 'YYYY-MM-DD HH24:MI:SS'));

The above query will help you change your string input to timestamps that are in year-month-date hour-minutes-seconds (TO_TIMESTAMP)

INSERT INTO your_table_name (timestamp_column) 

VALUES (SYSTIMESTAMP);

The above query will help you to give the current time value of your system with its time zone. ( SYSTIMESTAMP)

Answer 3

Whenever the need arises to add a timestamp in any timestamp column in an oracle database, the function TO_TIMESTAMP comes in handy. It assists in changing a timestamp from its string representation to the TIMESTAMP data type. Let us take a sample Orders table which has a TIMESTAMP edition of a column OrderTimestamp. In this case, you would first write the SQL INSERT statement and include the use of the TO_TIMESTAMP function. This function has two arguments; one is the stamp that you are planning to insert in a form of a string and two is the format, which is the outline of the string version of the stamp you are inserting. After typing this query, you are able to issue it to the database which will carry out an insertion of the timestamp value into the addressed column.

For example:

INSERT INTO Orders (OrderTimestamp)

VALUES (TO_TIMESTAMP('2024-10-30 15:30:00', 'YYYY-MM-DD HH24:MI:SS'));