Add two hours to timestamp

Question

I've got a trigger that has to set a timestamp to the current time plus two hours when the field is null before insert. One of the statements I tried so far is

IF :new.time_to_live IS NULL THEN
    :new.time_to_live := sysdate + INTERVAL '0 02:00:00.0' HOUR TO MINUTE;
END IF;

but I get a PLS-00166 Error (bad format for date, time, timestamp or interval literal) for the second row. Also modified it to several suggestions in multiple forums but the error stays. The column is created as follows:

time_to_live timestamp(0) NOT NULL

Answer 1

You need to change your HOUR TO MINUTE to match the value you're actually passing:

sysdate + INTERVAL '0 02:00:00.0' DAY TO SECOND

You might also want to use systimestamp instead of sysdate. You can use a shorter interval literal too if you're always adding exactly two hours:

systimestamp + INTERVAL '02:00' HOUR TO MINUTE

or just

systimestamp + INTERVAL '2' HOUR

As a quick demo:

SELECT systimestamp, systimestamp + INTERVAL '2' HOUR FROM DUAL;

SYSTIMESTAMP                        SYSTIMESTAMP+INTERVAL'2'HOUR      
----------------------------------- -----------------------------------
11-MAY-15 11.15.22.235029000 +01:00 11-MAY-15 13.15.22.235029000 +01:00