Insert SYSTIMESTAMP for Timestamp field

Question

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"

if you insert string `'2020-12-10 00:00:00'`, all you need it to use `To_date('2020-12-10 00:00:00', 'yyyy-mm-dd hh:mi:ss')` <-- check exact syntax with oracle — T.S., Jan 19 '21 at 21:13
Apologies - I'm not looking to insert the dates above - simply placeholders. I want the current system time to be inputted as part of the record for the timestamps. I want to replace the hardcoded dates with (now(), systimestamp() - IDK, these don't seem to work.) — overlord9314, Jan 19 '21 at 21:14
@T.S. `localdate` is not an Oracle function, and you missed `current_timestamp` — Wernfried Domscheit, Jan 19 '21 at 21:42
`sysdate`, `systimestamp`, `localtimestamp`, `current_timestamp` ____ @WernfriedDomscheit --> Danke — T.S., Jan 19 '21 at 22:18

score 1 · Answer 1 · answered Jan 19 '21 at 21:18

1

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.

answered Jan 19 '21 at 21:18

Gordon Linoff

1,242,037
58
646
786

If the OP asked about `systimestamp`, why would you show him how to use `current_timestamp` and only mention `systimestamp` at the end as an afterthought? At the very least, you might explain to him that the two do **different** things. One returns the session timestamp, the other the (database server) OS timestamp. They may reflect different time zones. – Jan 19 '21 at 22:21

score 0 · Answer 2 · 2021-01-19T22:24:45.800

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?

score 0 · Accepted Answer · answered Jan 19 '21 at 23:57

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.

Insert SYSTIMESTAMP for Timestamp field

3 Answers3