How to insert a timestamp in Oracle?
I have an Oracle DB with a
timestamp field in it. What is the correct SQL code to insert a
timestamp into this field?
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)
SYSTIMESTAMP are Oracle reserved words for this purpose. They are the timestamp analog of
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
If you have a time as a string and want to convert it to a timestamp, use an expression like
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);