How to insert a timestamp in Oracle?
How to insert a timestamp in Oracle?
Question
I have an Oracle DB with a timestamp field in it. What is the correct SQL code to insert a timestamp into this field?
Accepted Answer
insert
into tablename (timestamp_value)
values (TO_TIMESTAMP(:ts_val, 'YYYY-MM-DD HH24:MI:SS'));
if you want the current time stamp to be inserted then:
insert
into tablename (timestamp_value)
values (CURRENT_TIMESTAMP);
Read more… Read less…
INSERT
INTO mytable (timestamp_field)
VALUES (CURRENT_TIMESTAMP)
CURRENT_TIMESTAMP and SYSTIMESTAMP are Oracle reserved words for this purpose. They are the timestamp analog of SYSDATE.
INSERT INTO TABLE_NAME (TIMESTAMP_VALUE) VALUES (TO_TIMESTAMP('2014-07-02 06:14:00.742000000', 'YYYY-MM-DD HH24:MI:SS.FF'));
Kind of depends on where the value you want to insert is coming from. If you want to insert the current time you can use CURRENT_TIMESTAMP as shown in other answers (or SYSTIMESTAMP).
If you have a time as a string and want to convert it to a timestamp, use an expression like
to_timestamp(:timestamp_as_string,'MM/DD/YYYY HH24:MI:SS.FF3')
The time format components are, I hope, self-explanatory, except that FF3 means 3 digits of sub-second precision. You can go as high as 6 digits of precision.
If you are inserting from an application, the best answer may depend on how the date/time value is stored in your language. For instance you can map certain Java objects directly to a TIMESTAMP column, but you need to understand the JDBC type mappings.
I prefer ANSI timestamp literals:
insert into the_table
(the_timestamp_column)
values
(timestamp '2017-10-12 21:22:23');
More details in the manual: https://docs.oracle.com/database/121/SQLRF/sql_elements003.htm#SQLRF51062
Inserting date in sql
insert
into tablename (timestamp_value)
values ('dd-mm-yyyy hh-mm-ss AM');
If suppose we wanted to insert system date
insert
into tablename (timestamp_value)
values (sysdate);