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

Solution 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

Solution 2

Alther interval can really be convenient, I often write this as:

IF :new.time_to_live IS NULL THEN
    :new.time_to_live := sysdate + 2 / 24.0
END IF;

Adding an integer to a date is treated as a number of days. "2 / 24" is two hours.

Comments

[durette]

This recasts the value to DATE. An expression like this works: SELECT TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS.FF') FROM DUAL; but an expression like this breaks: SELECT TO_CHAR(SYSTIMESTAMP + 2 / 24, 'YYYY-MM-DD HH24:MI:SS.FF') FROM DUAL;

[Ahmed Chishti]

This one saved a lot of work for me after even 5 years