how to work with time only not with date in oracle apex

Question

i am trying to ask how can i work with only time in oracle apex because i need to create a shift definition scenario , where there is a master table that contains a duration for the shift and in the details table there is a start time and end time and a core start time and core end time and the end time needs to be based on the duration but i cant save data with time only

i have tried alot of ways one of them was i putted the details table source as SQL QUERY and i have made it like this

Mytbale

 CREATE TABLE "SHIFT_DEFINITIONS" 
   ( "ID" NUMBER
   , "DESCRIPTION" VARCHAR2(255)
, "COLOR" VARCHAR2(50)
, "PREFIX" VARCHAR2(10)
, "TYPE" VARCHAR2(50)
, "DURATION" NUMBER
, CONSTRAINT "SHIFT_DEFINITIONS_CON" PRIMARY KEY ("ID") USING INDEX ENABLE
, CONSTRAINT "SHIFT_DEFINITIONS_CHHECK" CHECK ( "ID" IS NOT NULL ) ENABLE ) ;

my timeslots table

 CREATE TABLE "TIME_SLOTS" ( "ID" NUMBER
, "DAY_MODEL_ID" NUMBER
, "START_TIME" VARCHAR2(11)
, "END_TIME" VARCHAR2(11)
, "CORE_START_TIME" VARCHAR2(11)
, "CORE_END_TIME" VARCHAR2(11) ) ;

my query

SELECT ID, 
       DAY_MODEL_ID,
       TO_CHAR(TO_DATE(START_TIME,'HH24:MI'),'HH12:MI AM')START_TIME,
       END_TIME, 
       CORE_START_TIME,
       CORE_END_TIME
       FROM TIME_SLOTS;

but it wont show me any time and also it gives me an error

ORA-01733: virtual column not allowed here

really need help if someone can

Answer 1

Before you dig yourself into a hole you are going to regret can you explain why start/and are not defined as DATE or TIMESTAMP. By defining them like that then you can do simple arithmetic.

See below for a function and how to call it.

What is the purpose of storing duration? If it really needs to be saved you may want to consider doing that as an INTERVAL definition.

create or replace function time_between ( p_what in varchar2, p_d1 in date, p_d2 in date ) 
return number 
as 
   l_result number; 
begin 
    select (p_d2-p_d1) * decode( upper(p_what), 'SS', 24*60*60, 'MI', 24*60, 'HH', 24, NULL ) into l_result from dual; 
  return l_result; 
end; 
/


select floor( time_between( 'ss', to_date('01-AUG-2022 12:02:04', 'dd-mon-yyyy hh24:mi:ss'), to_date('09-AUG-2022 18:22:34', 'dd-mon-yyyy hh24:mi:ss' ))) seconds FROM DUAL;

 SECONDS
 714029

select floor( time_between( 'mi', to_date('01-AUG-2022 12:02:04', 'dd-mon-yyyy hh24:mi:ss'), to_date('09-AUG-2022 18:22:34', 'dd-mon-yyyy hh24:mi:ss' ))) minutes FROM DUAL;

MINUTES
11900

select floor( time_between( 'hh', to_date('01-AUG-2022 12:02:04', 'dd-mon-yyyy hh24:mi:ss'), to_date('09-AUG-2022 18:22:34', 'dd-mon-yyyy hh24:mi:ss' ))) hours FROM DUAL;

HOURS
198