Reputation: 35
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
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
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
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