CODEWITHSUNDEEP
sqloracle-database
Mike Rifgin
Mike Rifgin

Reputation: 10745

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?

Upvotes: 116

Views: 590799

Answers (11)

Pathmanathan Sujeeve
Pathmanathan Sujeeve

Reputation: 41

The below sample working fine for me.

INSERT INTO "DB"."TBL" (UPDATEDDATETIME) 
VALUES (TO_TIMESTAMP('2022-07-12 17:16:05.551000000', 'YYYY-MM-DD HH24:MI:SS.FF'))

Upvotes: 0

Dave Costa
Dave Costa

Reputation: 48111

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.

Upvotes: 11

Else
Else

Reputation: 61

One can simply use

INSERT INTO MY_TABLE(MY_TIMESTAMP_FIELD)
VALUES (TIMESTAMP '2019-02-15 13:22:11.871+02:00');

This way you won't have to worry about date format string, just use default timestamp format.

Works with Oracle 11, have no idea if it does for earlier Oracle versions.

Upvotes: 2

user330315
user330315

Reputation:

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

Upvotes: 8

Mario The Spoon
Mario The Spoon

Reputation: 4859

For my own future reference:

With cx_Oracle use cursor.setinputsize(...):

mycursor = connection.cursor();

mycursor.setinputsize( mytimestamp=cx_Oracle.TIMESTAMP );
params = { 'mytimestamp': timestampVar };
cusrsor.execute("INSERT INTO mytable (timestamp_field9 VALUES(:mytimestamp)", params);

No converting in the db needed. See Oracle Documentation

Upvotes: 1

Ahuramazda
Ahuramazda

Reputation: 427

First of all you need to make the field Nullable, then after that so simple - instead of putting a value put this code CURRENT_TIMESTAMP.

Upvotes: 0

Sai Krishna
Sai Krishna

Reputation: 59

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);

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425291

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.

Upvotes: 38

VGuest
VGuest

Reputation: 1

CREATE TABLE Table1 (
id int identity(1, 1) NOT NULL,
Somecolmn varchar (5),
LastChanged [timestamp] NOT NULL)

this works for mssql 2012

INSERT INTO Table1 VALUES('hello',DEFAULT)

Upvotes: -8

Kash
Kash

Reputation: 341

INSERT INTO TABLE_NAME (TIMESTAMP_VALUE) VALUES (TO_TIMESTAMP('2014-07-02 06:14:00.742000000', 'YYYY-MM-DD HH24:MI:SS.FF'));

Upvotes: 29

reggie
reggie

Reputation: 13711

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);

Upvotes: 170

Related Questions