I have a table in my database with the attributes start and end time. These have the date format 'DD/MM/YYYY HH24: MI: SS'. I would like to add a default value for the end time, which is the result of the start time plus 45 minutes. Does anyone know how this could be implemented?

Kind regards

enter image description here

CodePudding user response：

You may use VIRTUAL column instead of a DEFAULT value

The virtual column is never inserted, but calculated in select from the given formula.

Example

create table lesson
(starttime DATE,
 endtime DATE generated always as (starttime    INTERVAL '45' MINUTE) virtual
 );
 
insert into lesson(starttime) values(sysdate);
 
select * from lesson; 

STARTTIME           ENDTIME            
------------------- -------------------
14.12.2021 19:11:34 14.12.2021 19:56:34

Real Default Value with Virtual Column

If you want to be able to override the dafault value and change the length of the lesson - add an other column with the exceptional end time and adapt the formula.

create table lesson
(starttime DATE,
 endtime_exept DATE,
 endtime DATE generated always as (
    case when endtime_exept is null then starttime    INTERVAL '45' MINUTE else endtime_exept end
    ) virtual
 );
 
insert into lesson(starttime) values(to_date('14.12.2021 10','dd.mm.yyyy hh24'));
 
select starttime, endtime from lesson; 

STARTTIME           ENDTIME            
------------------- -------------------
14.12.2021 10:00:00 14.12.2021 10:45:00

-- prolong the lesson
update lesson
set endtime_exept = to_date('14.12.2021 11','dd.mm.yyyy hh24');

select starttime, endtime from lesson;

STARTTIME           ENDTIME            
------------------- -------------------
14.12.2021 10:00:00 14.12.2021 11:00:00

CodePudding user response：

Use a BEFORE INSERT trigger and add an interval literal to the start time when the end time is NULL:

CREATE TRIGGER lesson_default_endtime
  BEFORE INSERT ON lesson
  FOR EACH ROW
DECLARE
BEGIN
  IF :new.endtime IS NULL THEN
    :new.endtime := :new.starttime   INTERVAL '45' MINUTE;
  END IF;
END;
/

If you:

-- Default value
INSERT INTO lesson (starttime, endtime) VALUES (DATE '2021-01-01', NULL);

-- Non-default value
INSERT INTO lesson (starttime, endtime) VALUES (DATE '2021-01-01', DATE '2021-01-01'   INTERVAL '1' HOUR);

Then:

SELECT starttime, endtime FROM lesson;

Outputs:

STARTTIME	ENDTIME
2021-01-01 00:00:00	2021-01-01 00:45:00
2021-01-01 00:00:00	2021-01-01 01:00:00

db<>fiddle here