CODEWITHSUNDEEP
sqloracle-database
Bufovis
Bufovis

Reputation: 29

SQL DEVELOPER: Add time to date format

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

Upvotes: 1

Views: 159

Answers (2)

Marmite Bomber
Marmite Bomber

Reputation: 21115

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

Upvotes: 1

MT0
MT0

Reputation: 168671

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

Upvotes: 0

Related Questions