CODEWITHSUNDEEP
oracle-databaseoracle11goracle10g
user2428207
user2428207

Reputation: 825

Add Minute and Seconds to Timestamp where Date is the same

Lets say I got Timestamp1

01.10.2013 10:00:00

Timestamp1 is the max(timestamp) from tbl1. Now I need Timestamp1 + seconds and minutes from max(timestamp2) from tbl2 where the DATE is the same.

Upvotes: 0

Views: 86

Answers (1)

Przemyslaw Kruglej
Przemyslaw Kruglej

Reputation: 8113

Try this:

CREATE TABLE tbl1 (
  timestamp1 TIMESTAMP
);

CREATE TABLE tbl2 (
  timestamp2 TIMESTAMP
);

INSERT INTO tbl1 VALUES (TO_TIMESTAMP('01.10.2013 09:00:00', 'DD.MM.YYYY HH24:MI:SS'));
INSERT INTO tbl1 VALUES (TO_TIMESTAMP('01.10.2013 10:00:00', 'DD.MM.YYYY HH24:MI:SS'));
INSERT INTO tbl1 VALUES (TO_TIMESTAMP('02.10.2013 10:32:54', 'DD.MM.YYYY HH24:MI:SS'));
INSERT INTO tbl1 VALUES (TO_TIMESTAMP('03.10.2013 10:00:00', 'DD.MM.YYYY HH24:MI:SS'));

INSERT INTO tbl2 VALUES (TO_TIMESTAMP('01.10.2013 12:24:33', 'DD.MM.YYYY HH24:MI:SS'));
INSERT INTO tbl2 VALUES (TO_TIMESTAMP('02.10.2013 12:46:11', 'DD.MM.YYYY HH24:MI:SS'));

COMMIT;

SELECT t1.max_time +
          NUMTODSINTERVAL(NVL(EXTRACT(MINUTE FROM t2.max_time), 0), 'MINUTE') +
          NUMTODSINTERVAL(NVL(EXTRACT(SECOND FROM t2.max_time), 0), 'SECOND') AS val
  FROM (SELECT TRUNC(timestamp1) date_part, MAX(timestamp1) max_time
          FROM tbl1
        GROUP BY TRUNC(timestamp1)) t1
    LEFT JOIN (SELECT TRUNC(timestamp2) date_part, MAX(timestamp2) max_time
            FROM tbl2
          GROUP BY TRUNC(timestamp2)) t2
    ON (t1.date_part = t2.date_part)
;

Output:

VAL                        
----------------------------
13/10/01 10:24:33,000000000  
13/10/02 11:19:05,000000000 
13/10/03 10:00:00,000000000

Upvotes: 1

Related Questions