CODEWITHSUNDEEP
oracle-databasesystimestamp
overlord9314
overlord9314

Reputation: 35

Insert SYSTIMESTAMP for Timestamp field

I have three timestamps in my SQL Table.

Column Name     Data Type   Nullable Data_Default

STATUS_TIMSTM   TIMESTAMP(6)    No  (null)
CREATED_TIMSTM  TIMESTAMP(6)    No  SYSTIMESTAMP  
UPDATED_TIMSTM  TIMESTAMP(6)    No  (null)

INSERT INTO "TABLE_NAME" ("STATUS_TIMSTM","CREATED_TIMSTM","UPDATED_TIMSTM")
VALUES(TIMESTAMP '2020-12-10 00:00:00', TIMESTAMP '2020-06-15 00:00:00',TIMESTAMP '2020-06-15 00:00:00');

The above works correctly.

How do I insert the current systimestamp?

I've tried several options: curdate(), now(), systimestamp().

I usually get errors such as Error report - SQL Error: ORA-00904: "NOW": invalid identifier 00904. 00000 - "%s: invalid identifier"

Upvotes: 0

Views: 2222

Answers (3)

Saiprasad Bane
Saiprasad Bane

Reputation: 487

Since you already have a DATA DEFAULT, only inserting data in below format must populate the CREATED_TIMSTM column with current TIMESTAMP.

INSERT INTO "TABLE_NAME" ("STATUS_TIMSTM","UPDATED_TIMSTM")
VALUES(TIMESTAMP '2020-12-10 00:00:00', TIMESTAMP '2020-06-15 00:00:00');

Here is a simplified DB fiddle demonstrating the same.

Upvotes: 0

user5683823
user5683823

Reputation:

In Oracle you would

insert into my_table(timestamp_column) values (systimestamp);

Notice that the function call does not include parentheses after the function name. Oracle is pretty odd in this regard; functions that don't take parameters, but that you define yourself, must use empty parentheses, but similar functions (no parameters) that are provided by Oracle must be used without parentheses. Only Oracle knows why it's inconsistent this way. This explains why your attempt was failing.

(Actually, some experimentation with systimestamp shows that it can take an argument - a positive integer which shows how many decimal places you want for seconds! In any case, you can't use it with empty parentheses.)

There are other "current" timestamp functions, but they do different things. systimestamp returns the timestamp of the computer system that hosts the database server. (Note that this may, and often is, different from the database timestamp.) In any case, systimestamp is by far the most commonly used of these; similar to sysdate for dates.

Beware of time zone though. systimestamp returns timestamp with time zone. By inserting it into a timestamp column, you are losing information. Is that OK for your business application?

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270793

You should be able to use current_timestamp:

create table t (x   TIMESTAMP(6));

insert into t (x) values (current_timestamp);

Of course, systimestamp should also work.

Here is a db<>fiddle.

Upvotes: 1

Related Questions