What is the datatype in oracle for a field whose datatype in DB2 is 'TIME' ? 'Interva-CodePudding

In DB2 one field datatype is 'TIME' value '18.09.58' . Now i need to create the same table in Oracle. While creating itself how could i declare that field ?

I tried 'Interval hour to second' and DATE 'datatype'. It is not working. Only this exact value '18.09.58' need to be inserted. Any suggestion please ?

CodePudding user response：

Use INTERVAL DAY(0) TO SECOND(0) (then the precision for the days and seconds is zero).

CREATE TABLE table_name (
  time INTERVAL DAY(0) TO SECOND(0)
);

then:

INSERT INTO table_name (time) VALUES (INTERVAL '18:09:58' HOUR TO SECOND);

works but:

INSERT INTO table_name (time) VALUES (INTERVAL '1 18:09:58' DAY TO SECOND);

Gives the exception:

ORA-01873: the leading precision of the interval is too small

fiddle

CodePudding user response：

If it must be 18.09.58, then it is a VARCHAR2(8), and that would be a bad idea.

Oracle doesn't have a "separate" datatype for time. Its DATE datatype contains both date and time. I'd suggest you to use it.

Because, if you choose to store values as strings, database can't do any validations and you'll be able to store e.g. a3:48/4% into the column, and that certainly isn't valid date (nor time) value.

If you don't care about time, then discard it while selecting values from the table. You can do it (for display purposes) using the TO_CHAR function with desired format model, e.g.

SQL> create table test (col date);

Table created.

SQL> insert into test values (sysdate);

1 row created.

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi.ss';

Session altered.

SQL> select * from test;

COL
-------------------
13.12.2022 14:20.38

SQL> select to_char(col, 'hh24:mi:ss') only_time from test;   --> this

ONLY_TIM
--------
14:20:38

SQL>