Select date in oracle db-CodePudding

I set or insert time in a postgres table value with now()::timestamp

But it does not work in oracle db.

This is the table:

create table types
(
    id          number                                                                                               not null,
    description varchar(255)                                                                                         not null,
    created_at  timestamp                                                                                            null,
    updated_at  timestamp                                                                                            null,
    deleted_at  timestamp                                                                                            null,
    CONSTRAINT autenticacion_id   PRIMARY KEY (id)

);

So to insert the data I make:

insert into types (id, description) values (1, 'hello world', now()::timestamp)

But I get:

ORA-00917: missing comma

insert into types (id, description) values (1, 'hello world', (select sysdate from dual))

I get:

ORA-00942: table or view does not exist

CodePudding user response：

You are providing 3 values and have only given 2 columns that you want to insert into.
SYSDATE is a DATE data type and SYSTIMESTAMP is a TIMESTAMP data type; they both have a date and time component but SYSTIMESTAMP also has fractional seconds (and a time zone, but that will be ignored as the column you are inserting into is a TIMESTAMP and not a TIMESTAMP WITH TIME ZONE data type).

What you probably want is:

INSERT INTO types (id, description, created_at)
VALUES (1, 'hello world', SYSTIMESTAMP)

However, you may want to consider that the timestamp should be in a specific time zone (typically UTC):

INSERT INTO types (id, description, created_at)
VALUES (1, 'hello world', SYSTIMESTAMP AT TIME ZONE 'UTC')

Note: You can wrap a value in (SELECT value FROM DUAL) but it is not necessary.

fiddle