CODEWITHSUNDEEP
sqloracle-databasedatedatetime
Python241820
Python241820

Reputation: 1527

How to add minutes to a date(timestamp) in Oracle?

I created the following tables:

create table travel
(
    code         VARCHAR2(3),
    date         timestamp,
    constraint pk_code_travel primary key(code)
);

create table extras
(
    code_extra          VARCHAR2(3),
    code_travel         VARCHAR2(3),
    minutes             NUMBER(3),
    constraint pk_code_extra primary key(code_extra)
);

I inserted the following dates:

insert into travel (code, date)
values('T01',TO_TIMESTAMP('10/01/2016 15:30','DD/MM/YYYY HH24:MI'));
insert into travel (code, date)
values('T02',TO_TIMESTAMP('15/02/2016 17:45','DD/MM/YYYY HH24:MI'));
insert into travel (code, date)
values('T03',TO_TIMESTAMP('01/04/2016 22:00','DD/MM/YYYY HH24:MI'));

insert into extras (code_extra, code_travel, minutes)
values('E01', 'T01', 50); 
insert into extras (code_extra, minutes)
values('E02', 'T02', 123); 
insert into extras (code_extra, minutes)
values('E03', 'T03', 48);

The question is: how to add the minutes from the table "extras" to the date of "travel" table ?

For example the table "travel" should be:

T01', 10/01/2016 15:30 + 50 minutes.

T02', 15/02/2016 17:45 + 123 'minutes.

T03', 01/04/2016 22:00 + 48 minutes.

All help will be appreciated.

Upvotes: 1

Views: 9162

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59436

You can create a table like this:

create table extras
(
    code_extra          VARCHAR2(3),
    code_travel         VARCHAR2(3),
    minutes             INTERVAL DAY TO SECOND(0),
    constraint pk_code_extra primary key(code_extra)
);

Then you can insert interval values by one of the following methods:

INSERT INTO extras (code_extra, code_travel, minutes)
VALUES('E01', 'T01', INTERVAL '50' MINUTE); 
INSERT INTO extras (code_extra, minutes)
VALUES('E02', 'T02', 123 * INTERVAL '1' MINUTE); 
INSERT INTO extras (code_extra, minutes)
VALUES('E03', 'T03', NUMTODSINTERVAL(48, 'MINUTE'));

Or as a select statement from your existing tables:

SELECT code, "DATE" + minutes * INTERVAL '1' MINUTE
FROM travel
    JOIN extras ON code = code_extra;

or 

SELECT code, "DATE" + NUMTODSINTERVAL(minutes, MINUTE)
FROM travel
    JOIN extras ON code = code_extra;

btw, you should not use reserved words like DATE as column name (that's the reason why I enclosed it by double-quotes)

Upvotes: 4

Related Questions